How to process multiple variables present in horizontally to vertically?

[sahoositaram555]

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

[PeterClemmensen]

Why are the values for drug1=L1 missing in your desired data set?

[sahoositaram555]

Apologies, Forgot to add that. Thanks for picking that out. No intention to leave that blank.

[PeterClemmensen]

But you do want to leave the obs for drug2, drug3 ... drug15 blank? besides the first variable?

[sahoositaram555]

No,There are values. I didn't mentioned the values of all due to space and policy issues. Sorry about that.

[Kurt_Bremser · Accepted solution]

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.