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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.