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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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