Help with Transposing the data

[pp2014]

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

[PGStats]

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

[pp2014]

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

[PGStats]

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