BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shasha11111
Fluorite | Level 6

Hi, I need to do some aggregation on a data, which the original format is a pivot table:

 11/1/1912/1/191/1/202/1/203/1/20
comp1valvalvalvalval
comp2valvalvalvalval
comp3valvalvalvalval

 

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.

comp111/1/19val
comp112/1/19val
comp11/1/20val
comp12/1/20val
comp13/1/20val
comp211/1/19val
comp212/1/19val
comp21/1/20val
comp22/1/20val
comp23/1/20val
comp311/1/19val
comp312/1/19val
comp31/1/20val
comp32/1/20val
comp33/1/20val
1 ACCEPTED SOLUTION
3 REPLIES 3
KachiM
Rhodochrosite | Level 12

@shasha11111 

 

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;

 

 

art297
Opal | Level 21

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

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1651 views
  • 3 likes
  • 4 in conversation