Help using Base SAS procedures

Transpose a Variable in SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Transpose a Variable in SAS

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


Accepted Solutions
Solution
‎04-13-2018 09:36 AM
PROC Star
Posts: 8,167

Re: Transpose a Variable in SAS

Posted in reply to Malathi13

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

 

View solution in original post


All Replies
Super User
Posts: 6,785

Re: Transpose a Variable in SAS

Posted in reply to Malathi13

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.

Frequent Contributor
Posts: 76

Re: Transpose a Variable in SAS

Posted in reply to Astounding

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

Super User
Posts: 6,785

Re: Transpose a Variable in SAS

[ Edited ]
Posted in reply to Malathi13

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.

Solution
‎04-13-2018 09:36 AM
PROC Star
Posts: 8,167

Re: Transpose a Variable in SAS

Posted in reply to Malathi13

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

 

Frequent Contributor
Posts: 76

Re: Transpose a Variable in SAS

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

 

Thank you
M

PROC Star
Posts: 1,836

Re: Transpose a Variable in SAS

Posted in reply to Malathi13
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;
Super User
Posts: 10,787

Re: Transpose a Variable in SAS

Posted in reply to Malathi13
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 179 views
  • 1 like
  • 5 in conversation