Hi all,
I have a dataset having details from drug 1 to drug type15 with every drug has 8 related columns .
so, in total 8(related columns )*15(no of drugs.). similar to sample dataset below.
Drug1 |
Dose1 |
Unit1 |
Route1 |
Frequency1 |
Indication1 |
Startdate1 |
Stopdate1 |
Drug2 |
Dose2 |
Unit |
Route |
Frequency |
Indication |
Startdate |
Stopdate |
X1 |
34 |
Mg |
Intravenous |
Q6h(every 6 hours) |
Pre event |
15/07/2017 |
15/08/2017 |
x2 |
|
|
|||||
Y1 |
3 |
Mmol |
Nasogastric |
Q3h(every 3 hours) |
Pre event |
12/07/2017 |
17/08/2017 |
y2 |
|
|
|||||
Z1 |
48 |
Mg |
Peritoneal |
Q8h(every 8 hours) |
To treat event |
23/08/2017 |
12/09/2017 |
z2 |
|
|
|||||
K1 |
23 |
Mmol |
intramuscular |
Q4h(every 4 hours) |
To treat event |
23/09/2017 |
21/12/2017 |
k2 |
|
|
|||||
L1 |
12 |
ML |
Intravenous |
Q5h(every 5 hours) |
. |
15/04/2017 |
13/08/2017 |
l2 |
|
|
I would like to create a data set in a way that it will look like below .
Drug |
Dose |
Unit |
Route |
Frequency |
Indication |
Startdate |
Stopdate |
X1 |
34 |
Mg |
Intravenous |
Q6h(every 6 hours) |
Pre event |
15/07/2017 |
15/08/2017 |
Y1 |
3 |
Mmol |
Nasogastric |
Q3h(every 3 hours) |
Pre event |
12/07/2017 |
17/08/2017 |
Z1 |
48 |
Mg |
Peritoneal |
Q8h(every 8 hours) |
To treat event |
23/08/2017 |
12/09/2017 |
K1 |
23 |
Mmol |
intramuscular |
Q4h(every 4 hours) |
To treat event |
23/09/2017 |
21/12/2017 |
L1 |
|
|
|
|
|
|
|
X2 |
|
|
|
|
|
|
|
Y2 |
|
|
|
|
|
|
|
Z2 |
|
|
|
|
|
|
|
K2 |
|
|
|
|
|
|
|
L2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
X3 |
|
|
|
|
|
|
|
Y3 |
|
|
|
|
|
|
|
Z3 |
|
|
|
|
|
|
|
K3 |
|
|
|
|
|
|
|
L3 |
|
|
|
|
|
|
|
.
.
Any ideas/comments how to proceed with this since individually making datasets(drug1,drug2,drug3) and then by using set command to place one over another looks like a time taking process. Anyone can help me with some ideas how to proceed with sas arrays or ny better method/methods than arrays would be really appreciated .
Thanks in advance.
With regards,
Ram
See this simplified example:
data have;
input drug1 $ dose1 drug2 $ dose2;
datalines;
X1 5 X2 10
Y1 3 Y2 5
;
data int;
set have;
array _drug {*} drug:;
array _dose {*} dose:;
number = _n_;
do i = 1 to dim(_drug);
drug = _drug{i};
dose = _dose{i};
output;
end;
keep number i drug dose;
run;
proc sort data=int out=want (drop=number i);
by i number;
run;
proc print data=want noobs;
run;
Result:
drug dose X1 5 Y1 3 X2 10 Y2 5
You need to add additional columns (and set formats) in the data step.
Why are the values for drug1=L1 missing in your desired data set?
But you do want to leave the obs for drug2, drug3 ... drug15 blank? besides the first variable?
See this simplified example:
data have;
input drug1 $ dose1 drug2 $ dose2;
datalines;
X1 5 X2 10
Y1 3 Y2 5
;
data int;
set have;
array _drug {*} drug:;
array _dose {*} dose:;
number = _n_;
do i = 1 to dim(_drug);
drug = _drug{i};
dose = _dose{i};
output;
end;
keep number i drug dose;
run;
proc sort data=int out=want (drop=number i);
by i number;
run;
proc print data=want noobs;
run;
Result:
drug dose X1 5 Y1 3 X2 10 Y2 5
You need to add additional columns (and set formats) in the data step.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.