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

From sasCommunity
Jump to: navigation, search

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;