topic Re: Convert a pivot table to column-based sas data in SAS Programming

Convert a pivot table to column-based sas data

shasha11111 — Thu, 21 Nov 2019 02:31:39 GMT

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

Re: Convert a pivot table to column-based sas data

ChrisNZ — Thu, 21 Nov 2019 03:21:36 GMT

Have you seen this page

https://blogs.sas.com/content/iml/2011/01/31/reshaping-data-from-wide-to-long-format.html

Re: Convert a pivot table to column-based sas data

KachiM — Thu, 21 Nov 2019 03:34:46 GMT

@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;

Re: Convert a pivot table to column-based sas data

art297 — Thu, 21 Nov 2019 04:05:34 GMT

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