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