Transpose a Variable in SAS

[Malathi13]

Hi,

I'm having difficulty  in transposing a variable. I don't use proc transpose that much, so I'm having problem with that procedure. I have a dataset that looks like:

ID        age   race  weight  Medication Administer_time

1        40      White  70       Tylenol           04/12/181:20:21

1         40     white   70      Advil              04/12/182:20:10

1        40       white  70     Motrin             04/12/183:30:15

2         50    Black  100    Tylenol            04/12/181:25:15

2         50    Black  100    Advil                04/12/181:25:15

2          50    Black  100    Benadryl         04/12/181:25:15

3        70   Asia      100    Claritin            04/12/181:25:15

3        70   Asia      100    Claritin            04/12/181:20:15

3        70   Asia      100    Bengay           04/12/181:30:15

3        70   Asia      100    Advil              04/12/181:40:15

I want to transpose Medication column and keep one observation per ID. How can I get the results? I want the output something like this:

ID        age   race  weight   Administer_time          Tylenol      Advil  Motrin  Benadryl  Claritin  Bengay

1        40      White  70     04/12/181:20:21             1                 1           1            0           0            0

2         50    Black  100    04/12/181:25:15              1               1             0           1            0            0

3        70   Asia      100    04/12/181:25:15              0                1           0            0            2            1

Any Help???

thank you

M

[Astounding]

Some items to consider before you program ...

First, you can see you will lose some data by doing this.  You used to have different administration times for each observation.  Now you are being forced to discard all but one of those.

Will the same person come back on some other day?  Do you propose to put all of those medications onto the same observation, or will you start a new observation?  If you include them on the same observation, could the WEIGHT measurement be different?  How do you propose to capture that?

How many different medications are in your data?  That could affect the choice of programming tools.

The real bottom line is what this transposition is for.  If it is a one-time report, that works.  But don't do this just to be able to program with your data.  Your current form of the data is both more accurate and more flexible for programming purposes.

[Malathi13]

Hi,

As long as I mention the frequency of the medications administered (Not worry about the administration time) that should be good. I have around 20 different medications (not all the twenty for each ID, some Id's have only 5 and some have 10 etc.). I do have other variables like dosage and Unit of dosage (that is not mentioned in my dataset here). If I get the idea of getting the output shown below, that would be great.

thank you

M

[Astounding]

OK, here's a starting point then:

options missing='0';

proc tabulate data=have missing;

class id age race weight medication;

var administer_time;

tables id age race weight, administer_time * min * f=datetime23. medication * n=' ' * f=10.;

run;

It assumes your Administer_time is actually a SAS date-time value, not a character string.  So if that's not the case,  you may need to fix the data first.

[art297 · Accepted solution]

The following involves a lot more code than what @Astounding suggested, but produces output that matches what you say you wanted/needed:

data have;
  informat Administer_time anydtdtm19.;
  format Administer_time datetime21.;
  input ID age   race $ weight  Medication $ Administer_time;
  cards;
1        40      White  70       Tylenol           04DEC2018:1:20:21
1         40     white   70      Advil             04DEC2018:2:20:10
1        40       white  70     Motrin             04DEC2018:3:30:15
2         50    Black  100    Tylenol            04DEC2018:1:25:15
2         50    Black  100    Advil                04DEC2018:1:25:15
2          50    Black  100    Benadryl         04DEC2018:1:25:15
3        70   Asia      100    Claritin            04DEC2018:1:25:15
3        70   Asia      100    Claritin            04DEC2018:1:20:15
3        70   Asia      100    Bengay           04DEC2018:1:30:15
3        70   Asia      100    Advil              04DEC2018:1:40:15
;

data need;
  set have;
  race=propcase(race);
  _count=1;
run;

proc format;
  value $meds
  'Advil'='Advil'
  'Benadryl'='Benadryl'
  'Bengay'='Bengay'
  'Claritin'='Claritin'
  'Tylenol'='Tylenol'
  ;
run;

proc means data=need nway completetypes NOPRINT;
  VAR _count;
  class medication/preloadfmt;
  class ID;
  format medication $meds.;
  output out=need2(drop=_:) N=count;
run;

proc sort data=need2;
  by id;
run;

proc sort data=have out=need3 nodupkey;
  by id;
run;

data need4;
  merge need3 (drop=medication) need2;
  by id;
run;

data need4;
  retain ID age race weight Administer_time;
  set need4;
run;

proc transpose data=need4 out=want (drop=_:);
  by id age race weight Administer_time;
  var count;
  id medication;
run;

Of course, if any SAS developers are listening, isn't it time we added preloadfmt and completetypes to PROC FREQ?

Art, CEO, AnalystFinder.com

[Malathi13]

Thank you so much for the help. Though the program is long, I got the results that I wanted. Perfect Solution!

Thank you
M

[novinosrin]

data have;
  informat Administer_time anydtdtm19.;
  format Administer_time datetime21.;
  input ID age   race $ weight  Medication $ Administer_time;
  cards;
1        40      White  70       Tylenol           04DEC2018:1:20:21
1         40     white   70      Advil             04DEC2018:2:20:10
1        40       white  70     Motrin             04DEC2018:3:30:15
2         50    Black  100    Tylenol            04DEC2018:1:25:15
2         50    Black  100    Advil                04DEC2018:1:25:15
2          50    Black  100    Benadryl         04DEC2018:1:25:15
3        70   Asia      100    Claritin            04DEC2018:1:25:15
3        70   Asia      100    Claritin            04DEC2018:1:20:15
3        70   Asia      100    Bengay           04DEC2018:1:30:15
3        70   Asia      100    Advil              04DEC2018:1:40:15
;


proc sort data=have(keep=medication) out=_have nodupkey;
by medication;
run;

proc transpose data=_have out=__have ;
var medication;
id medication;
run;


data want;
set have;
by id;
if _n_=1 then set __have;
array t(*) Advil--Tylenol;
if first.id then do _i=1 to dim(t);
t(_i)=0 ;
end;
do _i=1 to dim(t);
if vname(t(_i))=Medication then t(_i)=t(_i)+1 ;
end;
if last.id;
drop _:;
run;

[Ksharp]

data have;
  informat Administer_time anydtdtm19.;
  format Administer_time datetime21.;
  input ID age   race $ weight  Medication $ Administer_time;
  cards;
1        40      white  70       Tylenol           04DEC2018:1:20:21
1         40     white   70      Advil             04DEC2018:2:20:10
1        40       white  70     Motrin             04DEC2018:3:30:15
2         50    Black  100    Tylenol            04DEC2018:1:25:15
2         50    Black  100    Advil                04DEC2018:1:25:15
2          50    Black  100    Benadryl         04DEC2018:1:25:15
3        70   Asia      100    Claritin            04DEC2018:1:25:15
3        70   Asia      100    Claritin            04DEC2018:1:20:15
3        70   Asia      100    Bengay           04DEC2018:1:30:15
3        70   Asia      100    Advil              04DEC2018:1:40:15
;

proc freq data=have noprint;
table ID*age*race*weight*medication/out=temp list nopercent;
run;

proc transpose data=temp out=temp1(drop=_:) ;
by ID age   race  weight;
var count;
id medication;
run;
proc stdize data=temp1 out=temp2 missing=0 reponly;
run;

data id;
 set have;
 by id;
 if first.id;
 keep id Administer_time;
run;

data want;
 merge temp2 id;
 by id;
run;