BookmarkSubscribeRSS Feed
HarshadMadhamshettiwar
Obsidian | Level 7

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)Capture1.PNG

2 REPLIES 2
LinusH
Tourmaline | Level 20

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

Data never sleeps
Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 959 views
  • 0 likes
  • 3 in conversation