Help with repeating values dataset from Oracle Clinical

[deleted_user]

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

[Flip]

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]

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]

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]

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]

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]

Appreciate you help.

Addy