topic Re: Help with data using array in SAS Programming

Help with data using array

pp2014 — Fri, 28 Mar 2014 18:50:00 GMT

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

Re: Help with data using array

Reeza — Fri, 28 Mar 2014 19:31:42 GMT

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.

Re: Help with data using array

Jagadishkatam — Sat, 29 Mar 2014 04:30:17 GMT

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

Re: Help with data using array

pp2014 — Sat, 29 Mar 2014 12:16:14 GMT

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

Re: Help with data using array

data_null__ — Sat, 29 Mar 2014 13:10:52 GMT

Simple just add the missing months to your data.