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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.