topic Re: Transposing Data for Analysis in SAS Programming

Transposing Sas dataset

jhh197 — Fri, 04 Aug 2023 16:51:09 GMT

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

Re: Transposing Data for Analysis

Tom — Fri, 04 Aug 2023 16:23:29 GMT

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.

Re: Transposing Data for Analysis

PaigeMiller — Fri, 04 Aug 2023 16:33:27 GMT

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.

Re: Transposing Data for Analysis

jhh197 — Fri, 04 Aug 2023 16:50:40 GMT

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

Re: Transposing Data for Analysis

jhh197 — Fri, 04 Aug 2023 16:53:16 GMT

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

Re: Transposing Data for Analysis

Tom — Fri, 04 Aug 2023 17:24:01 GMT

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

Re: Transposing Data for Analysis

jhh197 — Fri, 04 Aug 2023 17:28:11 GMT

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

Re: Transposing Data for Analysis

Tom — Fri, 04 Aug 2023 17:41:52 GMT

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.

Re: Transposing Data for Analysis

PaigeMiller — Fri, 04 Aug 2023 18:47:25 GMT

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

Re: Transposing Data for Analysis

jhh197 — Fri, 04 Aug 2023 18:55:42 GMT

Thank you so much for all your help

Re: Transposing Data for Analysis

jhh197 — Fri, 04 Aug 2023 19:08:14 GMT

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

Re: Transposing Data for Analysis

PaigeMiller — Sat, 05 Aug 2023 10:34:16 GMT

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