I have data as follows:
data have;
input date1 $6. mktid $2. drgid $2. terrid $2. measure $15. brand_count 5.;
cards;
201310 11 6 23 value 145
201310 11 6 23 unit 14
201311 11 6 23 value 123
201311 11 6 23 unit 10
201312 11 6 23 value 310
201312 11 6 23 unit 15
201401 11 6 23 value 225
201401 11 6 23 unit 13
I want to transpose the above using array.
Output wanted as follows:
mktid drgid terrid measure brand_count_mth4 brand_count_mth3 brand_count_mth2 brand_count_mth1
11 6 23 value 145 123 310 225
11 6 23 unit 14 10 15 13
Also based on above data, I want another output as follows:
MTH1 MTH2 MTH3 MTH4
201401 201312 201311 201310
Thanks
P
You should post this as a question, not a discussion.
Its also helpful to post what you tried.
Proc transpose is one way to do this.
Please try
data have;
input date1 :$6. mktid $2. drgid $2. terrid $4. measure $8. brand_count 5.;
cards;
201310 11 6 23 value 145
201310 11 6 23 unit 14
201311 11 6 23 value 123
201311 11 6 23 unit 10
201312 11 6 23 value 310
201312 11 6 23 unit 15
201401 11 6 23 value 225
201401 11 6 23 unit 13
;
proc sort data=have;
by mktid drgid terrid descending measure descending date1 ;
run;
proc transpose data= have out=want1 prefix=brand_count_mht;
by mktid drgid terrid descending measure ;
var brand_count ;
run;
proc transpose data= have out=want2 prefix=mht ;
by mktid drgid terrid descending measure ;
var date1 ;
run;
proc sort data=want2 out=want2_(drop=_name_ mktid drgid terrid measure ) nodupkey;;
by mktid drgid terrid ;
run;
Thanks,
Jag
Jag,
Thanks a lot for the solution. But if there is a missing month in between then it does not work.
For example:
data have;
input date1 :$6. mktid $2. drgid $2. terrid $4. measure $8. brand_count 5.;
cards;
201310 11 6 23 value 145
201310 11 6 23 unit 14
201312 11 6 23 value 310
201312 11 6 23 unit 15
201401 11 6 23 value 225
201401 11 6 23 unit 13
;
It gives me the following output which is not correct:
mktid drgid terrid measure brand_count_mth1 brand_count_mth2 brand_count_mth3
11 6 23 value 225 310 145
11 6 23 unit 13 15 14
Thanks
P
Simple just add the missing months to your data.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.