topic Re: Many rows to single row by using array in SAS Programming

Many rows to single row by using array

GeethaMN — Fri, 06 Oct 2017 10:29:03 GMT

HI,

Please find the below table is input dataset source.

ID	visit	weight
1001	weight_v1	56.5
1001	weight_v2	67.5
1001	weight_v3	62.8
1002	weight_v1	78.9
1002	weight_v2	75.3
1002	weight_v3	70.2
1003	weight_v1	67.7
1003	weight_v2	64.8
1003	weight_v3	63.9

I want to restructure this dataset into single row from many rows by using array only not by using transpose. so the output should be like this as follows:

Id	weight_v1	weight_v2	weight_v3
1001	56.5	67.5	62.8
1002	78.9	75.3	70.2
1003	67.7	64.8	63.9

Please help me on this. Thanks in advance.

Re: Many rows to single row by using array

RW9 — Fri, 06 Oct 2017 10:40:33 GMT

Why, is this a learning task? If so then look at the documentation, the many examples out on the web, and then write the code yourself, its very simple. In one datastep you will need an array statement, a retain statement, and a by statement, and only output on last record.

If it is not a learning exercise, then why are you restricting yourself to arrays, the example you give is exactly what proc transpose is built, tested, and optimised for, not using it is like writing code only using your thumbs!

Re: Many rows to single row by using array

Kurt_Bremser — Fri, 06 Oct 2017 10:43:16 GMT

Don't be a fool, use the right tool.

(Hey, it's rhyming)

The right tool is proc transpose.

Re: Many rows to single row by using array

SAS_inquisitive — Fri, 06 Oct 2017 14:45:25 GMT

data have;
input ID	visit $ 15.	weight;
cards;
1001	weight_v1	56.5
1001	weight_v2	67.5
1001	weight_v3	62.8
1002	weight_v1	78.9
1002	weight_v2	75.3
1002	weight_v3	70.2
1003	weight_v1	67.7
1003	weight_v2	64.8
1003	weight_v3	63.9
;
run;

data want;
	set have;
	by id notsorted;
	array ar{*} weight_v1 weight_v2 weight_v3;
	retain weight_v:;
	visit_ = input(substr(visit,9,1),best.);
	if first.id then call missing (of ar{*});
	
		do visit_ = 1 to dim(ar);
			ar{visit_}=weight;
		end;

	if last.id then output;
	drop visit weight visit_ ;
run;