Transposing Sas dataset

[jhh197]

I have a dataset which looks like below . 

Date	PRICE_DS	Loc_typ
201301	65	25 MN SK
201303	65	25 MN SK
201310	65	25 MN SK
201312	65	25 MN SK
201401	65	25 MN SK
201402	65	25 MN SK
201301	79.25	SP NC KPP
201303	79.25	SP NC KPP
201310	79.25	SP NC KPP
201312	79.25	SP NC KPP
201401	79.25	SP NC KPP
201402	79.25	SP NC KPP

I want to create output like below . Can anyone please help ?

Loc_typ	_1_1_2013	_3_1_2013	_10_1_2013	_12_1_2013	_1_1_2014	_2_1_2014
25 MN SK	65	65	65	65	65	65
SP NC KPP	79.25	79.25	79.25	79.25	79.25	79.25

[Tom]

Does not look useful for ANALYSIS.

But if you want to see the data in the style for people to look at you could do it with a report.

proc report data=have ;
  columns loc_typ price_ds,date;
  define loc_typ / group;
   define date / across ' ';
run;

Also your use of dates seems very confused. 

Does the original DATE variable have actual date values that is using the YYMMN6. format to display the date in that YYYYMM style? 

Or is it a character variable?  In which case use INPUT() with the YYMMN informat to convert the string into date values.

Or perhaps a numeric variable that instead of having actual date values just has integers in YYY,YMM style?  If so then use PUT() and INPUT() functions to convert the number into a string that you can convert into a date.

Why would you want to display the dates in the headers using MDY order?  If you put dates into text (variable names are text) you should always use YMD order so the text values will sort in chronological order.

[jhh197]

hi ,

Thank you for the reply . Can we use proc transpose instead ? Also, _1_1_2013 this is numeric column which has format and informat as BEST12.

Thank you 

[Tom]

So if you have a numeric variable named NUMBER with numbers like 202,303 and 202,210  and you want to convert them into the numeric variable named DATE with the dates '01MAR2023'd and '01OCT2022'd, respectively, then you could use PUT() and INPUT().

Make sure to attach a date type format to the variable so the values print in a way that humans will understand.

date = input(put(number,z6.),yymmn6.);
format date yymmdd10.;

Or you could use arithmetic and the MDY() function.

date = mdy(mod(number,100),1,int(number/100));
format date yymmdd10.;

[jhh197]

I would like to have the output like below . And the date field in input table looks like numeric and format7. And 201303 to _3_1_2013 numeric best12. Can i have the code in form of proc transpose ?

Loc_typ	_1_1_2013	_3_1_2013	_10_1_2013	_12_1_2013	_1_1_2014	_2_1_2014
25 MN SK	65	65	65	65	65	65
SP NC KPP	79.25	79.25	79.25	79.25	79.25	79.25

Thank you 

[Tom · Accepted solution]

You could generate those gibberish variable names by first calculating MONTH, DAY and YEAR from the existing "date" variable and then using all three in the ID statement in PROC TRANSPOSE.

data for_transpose;
  set have;
  month = mod(date,100);
  day=1;
  year = int(date/100);
run;

proc transpose data=for_transpose out=want(drop=_name_) delim=_ prefix=_;
   by loc_typ;
   id month day year;
   var price_ds;
run;

By making separate variables PROC TRANSPOSE will convert them to strings and remove the leading and trailing spaces so you can get that strings that sometimes have one digit for the day or month value and sometimes have two digits.

If instead you want to make variable names that are easier to work with (and less confusing to figure out what date represent) why not just use the data directly?

proc transpose data=have out=want(drop=_name_) prefix=YM_;
   by loc_typ;
   id date;
   var price_ds;
run;

So that you get variable named like YM_202301 and YM_202303.

[jhh197]

Thank you so much for all your help 

[jhh197]

It creates output how i wanted it . Can we add the label like 3/1/2013 . When i click on properties of _3_1_2013   it should show label as 3/1/2013 . 

Thank you for all your help 

[PaigeMiller]

This is sooooo much easier if you don't transpose in the first place. We can help you find easier ways to program this, if only you would tell us what you are trying to do (after you create this wide data set).

[PaigeMiller]

Agreeing with @Tom , there's really no value in doing this transformation for ANALYSIS. So its very easy, don't transpose the data, and this part of the task is done.

Reporting is different.

[jhh197]

Its just for transposing . I edited the message not for analysis

[PaigeMiller]

---

@jhh197 wrote:
Its just for transposing . I edited the message not for analysis

---

Don't do it. The variable names would be a nightmare to use in any further programming. Please tell us what you plan to do with this data after transposing it?