concatenate last observation variable value

[Rohit12]

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

[PeterClemmensen]

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;

[Rohit12]

I need this thing through proc sql 

[Kurt_Bremser]

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

[RW9 · Accepted solution]

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.

[Kurt_Bremser]

---

@Rohit12 wrote:

I need this output through proc sql

---

Maxim 14.