BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

I have data as follows:

data have;

  input date1 $6. mktid $2. drgid  $2. terrid $2. measure $15.  brand_count 5.;

cards;

201310 11 6  23  value 145

201310 11 6  23  unit  14

201311 11 6  23  value 123

201311 11 6  23  unit  10

201312 11 6  23  value 310

201312 11 6  23  unit  15

201401 11 6  23  value 225

201401 11 6  23  unit  13

I want to transpose the above using array.

Output wanted as follows:

mktid  drgid  terrid  measure  brand_count_mth4   brand_count_mth3    brand_count_mth2    brand_count_mth1  

               

11      6      23      value       145               123                   310                 225

11      6      23      unit         14               10                     15                  13

Also based on above data, I want another output as follows:

MTH1     MTH2      MTH3       MTH4

201401   201312   201311     201310

Thanks

P

4 REPLIES 4
Reeza
Super User

You should post this as a question, not a discussion.

Its also helpful to post what you tried. 

Proc transpose is one way to do this.

Jagadishkatam
Amethyst | Level 16

Please try

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

201311 11 6  23  value 123

201311 11 6  23  unit  10

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;

proc transpose data= have out=want2 prefix=mht ;   

    by mktid  drgid  terrid  descending measure  ;

    var  date1 ;

run;

proc sort data=want2 out=want2_(drop=_name_ mktid  drgid  terrid   measure ) nodupkey;;

    by     mktid  drgid  terrid ;

run;

Thanks,

Jag

Thanks,
Jag
pp2014
Fluorite | Level 6

Jag,

Thanks a lot for the solution. But if there is a missing month in between then it does not work.

For example:

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

;

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

Thanks

P

data_null__
Jade | Level 19

Simple just add the missing months to your data.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 547 views
  • 0 likes
  • 4 in conversation