Help with data using array

Reply
Frequent Contributor
Posts: 127

Help with data using array

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

Super User
Posts: 19,792

Re: Help with data using array

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.

Trusted Advisor
Posts: 1,137

Re: Help with data using array

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
Frequent Contributor
Posts: 127

Re: Help with data using array

Posted in reply to Jagadishkatam

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

Respected Advisor
Posts: 3,799

Re: Help with data using array

Simple just add the missing months to your data.

Ask a Question
Discussion stats
  • 4 replies
  • 236 views
  • 0 likes
  • 4 in conversation