BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi Group,
I have a dataset(Meds) from Oracle Clinical. It looks something like this

ID Vi Y_N Nam Dose
100 1 Y Dilt 10mg
100 1 Rel 10mg
100 1 Sy 69mg
101 1 Y RG 30mg
101 1 Cal 20mg


so now I would like to flip this around to one row for ID*Vi like this below

ID VI Y_N MD1 MD2 MD3 DS1 DS2 DS3
100 1 Y Dilt Rel SY 10mg 10mg 9mg
101 1 Y RG Ca . 30mg 20mg .


Here is what I did. I'm new to SAS so my code looks funny. HEre is it.

Proc sort data=Meds; by idno vi;Run;

Data Med1;set Meds;by idno vi;if first.idno;
Keep idno vi y_n;Run;


proc transpose data=Meds out=Med2 prefix=Md_Nm;
by idno vi;
Var NAM ;
run;


proc transpose data=Meds out=Med3 prefix=Md_Ds;
by idno vi;
Var DOSE ;
run;

Now I merge these three datasets ;

Data Medications;merge med1 med2 med3;by idno vi;Run;


I can use this method here(just two variables name and dose) but if I have to use the same for a dataset with 20 variables it is too much.


Can someone suggest me a way around to solve this issue?

Thanks for your time.

Regards,
Addy
6 REPLIES 6
Flip
Fluorite | Level 6
You can get more control over transposing using a datastep with arrays. Here is a small example that may help you out.

data two(drop = Y_N nam Dose);
set one;
by ID Vi ;
retain med1-med3 dose_1-dose_3 nY_N;
array med (3) $;
array dose_ (3) $;
if first.vi then do;
i = 0;
nY_N = Y_N;
end;
i+1;
med(i) = nam;
dose_(i) = dose;
if last.vi then output;

run;
deleted_user
Not applicable
Hey Flip,
I implemented you approach on my dataset and everything works fine except for one.
:-(

If you see in the example 101-1 has only two meds and doses but in the output dataset , I don't know why but even 101-1 has three meds and doses. The third one is same as 100-1 records 3rd med name and dose which is "SY" and "69mg".

Thanks,

Addy
Flip
Fluorite | Level 6
Sorry about that. With the retain in the if first.xxx set all the values to empty or missing. Otherwise the values from the last time are retained.

So you wil need to loop over the array and set med(i) = ' '
deleted_user
Not applicable
I get error if I use this. may be I have placed the IF statement in wrong order. I did put med(i)='' ; dose_(i)='' in the same if first.vi statement but still getting error.

data two(drop = Y_N nam Dose);
set one;
by ID Vi ;
retain med1-med3 dose_1-dose_3 nY_N;
array med (3) $;
array dose_ (3) $;
if first.vi then do;
med(i)='';dose_(i)='';
end;
if first.vi then do;
i = 0;
nY_N = Y_N;
end;
i+1;
med(i) = nam;
dose_(i) = dose;
if last.vi then output;

run;


Thanks
Addy
Flip
Fluorite | Level 6
ALmost there, you need to loop i over the range of values as:
data two(drop = Y_N nam Dose);
set one;
by ID Vi ;
retain med1-med3 dose_1-dose_3 nY_N;
array med (3) $;
array dose_ (3) $;
if first.vi then do;
do i = 1 to 3;
med(i)='';dose_(i)='';
end;
i = 0;
nY_N = Y_N;
end;
i+1;
med(i) = nam;
dose_(i) = dose;
if last.vi then output;

run;
deleted_user
Not applicable
Appreciate you help.

Addy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 6 replies
  • 1204 views
  • 0 likes
  • 2 in conversation