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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: