As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.
Here are some tips for How to share your SAS knowledge with your professional network.
Reading excel pivot tables
I am trying to read the data cells from an Excel Pivot table into SAS. The raw data is not available to me. The problem I encounter is how to 'fill' the dimension cells with the correct values. As an example, I pasted the data from excel below (after cleaning up tabs to spaces); the first row is the column name.
Sales_Date Market_Code Offer PFDP Rejected_by_FDR Confirmed TOTAL 20080601 3AP- Enrollment at time of Credit App - - - 305 305 3APX- 3APX - - - 10 10 9AP- Enrollment at time of Credit App - - - 700 700 AFR- Inbound CTA- New- RSF NEW: Telatron - - - 22 22 ALR- Inbound CTA- New- Results- LR - - 2 352 354 AVR- Inbound CTA- New- Results - IVR - - 1 45 46 BLR- Inbound CTA- Re-Issue- Results LR - - - 16 16 BVR- Inbound CTA- Re-Issue- Results IVR - - - 4 4 20080602 3AP- Enrollment at time of Credit App - - - 338 338 3APX- 3APX - - - 21 21 4AP- Enrollment at time of Credit App - - - 16 16 9AP- Enrollment at time of Credit App - - - 636 636
I saved the data from the pivot into a CSV file, and I am trying to read into SAS. The problem I encounter is that I need to have the 'Sales-Date' populated all the way down with the proper value. I tried PROC IMPORT, DATA step using RETAIN statement and LAG function, but I haven't been able to get it to work! There are many more dimensions I need in the PIVOT, but I've simplified for this example. If I can get the first column read correctly, I should be able to figure out the rest!
Input Data file: Media:Weekly_Sales_Date_Reporting.csv.txt
Here's the solution using the RETAIN statement thanks to Yu Zhang! Thanks So much!
data test; infile 'c:\test.txt' lrecl=500 dsd truncover firstobs=2; length salesdate $20. vendor $30 marketcode $60. cgroup $50.; input SalesDate $ MarketCode $ Offer $ PFDP $ Rejected $ Confirmed $ TOT $; run; data test; retain nmsale ; set test; if not missing(salesdate) then nmsale=salesdate; else salesdate=nmsale; drop nm:; run;