Hi, I need to do some aggregation on a data, which the original format is a pivot table:
11/1/19 | 12/1/19 | 1/1/20 | 2/1/20 | 3/1/20 | |
comp1 | val | val | val | val | val |
comp2 | val | val | val | val | val |
comp3 | val | val | val | val | val |
My goal is to transform this table into column-based dataset. Any suggestion on the programming? Tried roc transpose but it does not work on this case.
comp1 | 11/1/19 | val |
comp1 | 12/1/19 | val |
comp1 | 1/1/20 | val |
comp1 | 2/1/20 | val |
comp1 | 3/1/20 | val |
comp2 | 11/1/19 | val |
comp2 | 12/1/19 | val |
comp2 | 1/1/20 | val |
comp2 | 2/1/20 | val |
comp2 | 3/1/20 | val |
comp3 | 11/1/19 | val |
comp3 | 12/1/19 | val |
comp3 | 1/1/20 | val |
comp3 | 2/1/20 | val |
comp3 | 3/1/20 | val |
Here is small example for you.
data have;
input id $ dt1 dt2 dt3 dt4 dt5;
datalines;
comp1 10 11 12 13 14
comp2 20 21 22 23 24
comp3 30 31 32 33 34
;
run;
proc transpose data = have out = want(rename=(_NAME_=Dt COL1=VAL));
by id;
run;
Assuming (1) your pivot table is a tab delimited text file and (2) the three fields you want to create are called area, date and value, then the following might work:
data want (drop=_:);
infile '/folders/myfolders/pivot.txt' dlm='09'x dsd;
array _dates{5} $;
retain _dates;
format date mmddyy8.;
if _n_ eq 1 then input (_dummy _dates1-_dates5) ($);
else do;
input area $ @;
do _i=1 to 5;
input value @;
date=input(_dates(_i),mmddyy8.);
output;
end;
end;
run;
Art, CEO, AnalystFinder.com
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.