topic Re: How to convert sas dataset which is in columnar format into flat file? in SAS Programming

How to convert sas dataset which is in columnar format into flat file?

HarshadMadhamshettiwar — Sun, 20 Jul 2014 15:56:09 GMT

Hi, Need some suggestion regarding flat file data.

I have a SAS dataset which is imported as an excel file in SAS which was originally in a pivot report form. Now I want to convert this SAS dataset into flat style.

How should I do It??(I can explain in step wise manner)

1. An excel file(which is pivot report of some sort of sales data)

2.Imported successfully in sas as SAS dataset

3.How to change orientation of data so it is flat structure.????

I have already been able to import the file as sas dataset but it its orientation is pivot report format. i.e. I have 1 variable "products" (which is having ten entries) +36 other variables which are names of months spread across 3 years. Now what i want is all the 36(month)variables to be transposed into one column say "MONTHS". and this 36 months will repeat for all 10 products (each product*36 months i.e total 360 rows). so that i will get something like this(please refer image)

Re: How to convert sas dataset which is in columnar format into flat file?

LinusH — Sun, 20 Jul 2014 20:25:10 GMT

Why do you want to transpose the data? Sounds like a report requirement. If so, take a look at PROC TABULATE or PROC REPORT.

Re: How to convert sas dataset which is in columnar format into flat file?

Patrick — Mon, 21 Jul 2014 01:09:23 GMT

That you want the data in a long instead of a wide structure makes sense to me. Below code sample should get you started.

I've also added a variable "date" which you also could create with your real data if you know which date the first month starts from. A SAS Date variable gives you more flexibility (and you still can you formats or functions if you just want to show the month number).

data have(drop=_:);

length product $4;

array month {36} 8. ;

do _i=1 to dim(month);

month[_i]=_i;

end;

do product='PRD1','PRD2','PRD3';

output;

end;

stop;

run;

data want(drop=_: month1-month36);

set have;

format date date9.;

array month_arr {*} month1-month36;

do _i=1 to dim(month_arr);

amount=month_arr[_i];

date=intnx('month','01Jan2012'd,_i-1,'b');

output;

end;

run;