I am using the following code to transpose the data: Data has some missing month in it.
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
;
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;
It gives me the following output which is not correct:
mktid drgid terrid measure brand_count_mth1 brand_count_mth2 brand_count_mth3 brand_count_mth4
11 6 23 value 225 310 . 145
11 6 23 unit 13 15 . 14
Output I wanted is the following:
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
Any help will be highly appreciated..
Thanks
P
The problem doesn't reside within the example you gave us. Run the following on the whole dataset to find out where the problem lies :
proc sql;
select mktid, drgid, terrid, measure, date1, count(*) as n
from have
group by mktid, drgid, terrid, measure
having count(*) > 3;
quit;
PG
I am sorry I am reposting it. I have the following data
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
;
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;
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
Output I wanted is the following:
mktid drgid terrid measure brand_count_mth1 brand_count_mth2 brand_count_mth3 brand_count_mth4
11 6 23 value 225 310 . 145
11 6 23 unit 13 15 . 14
Any help will be highly appreciated..
Thanks
P
The number of brand_count_mth columns in the transposed output will correspond to the maximum number of observations per mktid-drgid-terrid-measure combination. One way to add a new column to the transposed output is to add a fourth (dummy) observation in the input dataset to a group which already has three.
You could also add the missing column(s) to the output in a following datastep.
data want2;
array x brand_count_mht1-brand_count_mht4;
set want1;
run;
PG
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.