DATA Step, Macro, Functions and more

concatenate last observation variable value

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

concatenate last observation variable value

data new4;
infile datalines missover ;
input x y $ z $;
datalines;
1 a b
1 a c
2 b c
2 b d
3 r t
3 r y
;
run;


I want my output to be
A new column need to be generated ICD whose value will be
x y ICD
1 a b, c
2 b c,d
3 r t,y

I need this output through proc sql


Accepted Solutions
Solution
‎12-12-2017 10:54 PM
Super User
Super User
Posts: 9,227

Re: concatenate last observation variable value

Proc sql does not do transposed data.  It is a language based on RDBMS, which is a normalised relational model.  To achieve the result through SQL, you would need to join each group back to the first group, which may be feasible if you have 1 or 2 groups, however if you have more than that then your code will become a mess.  Transposed data is only useful for review files - i.e. reports created for users.  It is very far from optimal when it comes to programming.  Thus first consider if you really want to do this, for what purpose, then use the right tool to do the job.

View solution in original post


All Replies
PROC Star
Posts: 1,190

Re: concatenate last observation variable value

data new4;
infile datalines missover ;
input x y $ z $;
datalines;
1 a b
1 a c
2 b c
2 b d
3 r t
3 r y
;
run;

proc sort data=new4;
	by x;
run;

data want;
	set new4;
	by x;
	if first.x then ICD=z;
	else ICD=catx(',',ICD,z);
	if last.x;

	retain ICD;
	keep x y ICD;
run;
Contributor
Posts: 47

Re: concatenate last observation variable value

I need this thing through proc sql 

Super User
Posts: 9,610

Re: concatenate last observation variable value

Not using the right tool is plain stupid. @draycut's suggestion aptly demonstrates the power of the data step for such a task.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎12-12-2017 10:54 PM
Super User
Super User
Posts: 9,227

Re: concatenate last observation variable value

Proc sql does not do transposed data.  It is a language based on RDBMS, which is a normalised relational model.  To achieve the result through SQL, you would need to join each group back to the first group, which may be feasible if you have 1 or 2 groups, however if you have more than that then your code will become a mess.  Transposed data is only useful for review files - i.e. reports created for users.  It is very far from optimal when it comes to programming.  Thus first consider if you really want to do this, for what purpose, then use the right tool to do the job.

Super User
Posts: 9,610

Re: concatenate last observation variable value


Rohit12 wrote:


I need this output through proc sql


Maxim 14.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 107 views
  • 0 likes
  • 4 in conversation