BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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

3 REPLIES 3
PGStats
Opal | Level 21

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

PG
pp2014
Fluorite | Level 6

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
Opal | Level 21

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

PG

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1135 views
  • 1 like
  • 2 in conversation