DATA Step, Macro, Functions and more

Many rows to single row by using array

Reply
Occasional Contributor
Posts: 15

Many rows to single row by using array

HI,

 

Please find the below table is input dataset source.

 

IDvisitweight
1001weight_v156.5
1001weight_v267.5
1001weight_v362.8
1002weight_v178.9
1002weight_v275.3
1002weight_v370.2
1003weight_v167.7
1003weight_v264.8
1003weight_v363.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_v1weight_v2weight_v3
100156.567.562.8
100278.975.370.2
100367.764.863.9

 

Please help me on this. Thanks in advance.

SAAAS
Super User
Super User
Posts: 7,997

Re: Many rows to single row by using array

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! 

Super User
Posts: 7,866

Re: Many rows to single row by using array

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

(Hey, it's rhyming)

 

The right tool is proc transpose.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 271

Re: Many rows to single row by using array

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;
Ask a Question
Discussion stats
  • 3 replies
  • 120 views
  • 5 likes
  • 4 in conversation