topic Help with Transposing the data in SAS Programming

Help with Transposing the data

pp2014 — Sat, 29 Mar 2014 17:43:48 GMT

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

Re: Help with Transposing the data

PGStats — Sat, 29 Mar 2014 18:45:13 GMT

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;

Re: Help with Transposing the data

pp2014 — Sat, 29 Mar 2014 19:05:33 GMT

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

Re: Help with Transposing the data

PGStats — Sat, 29 Mar 2014 19:45:33 GMT

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;