topic Re: Transpose a Variable in SAS in SAS Procedures

Transpose a Variable in SAS

Malathi13 — Thu, 12 Apr 2018 20:15:02 GMT

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

Re: Transpose a Variable in SAS

Astounding — Thu, 12 Apr 2018 20:31:56 GMT

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.

Re: Transpose a Variable in SAS

Malathi13 — Thu, 12 Apr 2018 21:31:14 GMT

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

Re: Transpose a Variable in SAS

Astounding — Thu, 12 Apr 2018 21:49:43 GMT

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.

Re: Transpose a Variable in SAS

art297 — Thu, 12 Apr 2018 22:33:36 GMT

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

Re: Transpose a Variable in SAS

novinosrin — Thu, 12 Apr 2018 23:37:39 GMT

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;

Re: Transpose a Variable in SAS

Malathi13 — Fri, 13 Apr 2018 13:37:48 GMT

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

Thank you
M

Re: Transpose a Variable in SAS

Ksharp — Fri, 13 Apr 2018 13:49:00 GMT

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;