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 |
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.
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.
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
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.;
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
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.
Thank you so much for all your help
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
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).
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 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?
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
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.
Ready to level-up your skills? Choose your own adventure.