topic concatenate last observation variable value in SAS Programming

concatenate last observation variable value

Rohit12 — Mon, 11 Dec 2017 10:37:59 GMT

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

Re: concatenate last observation variable value

PeterClemmensen — Mon, 11 Dec 2017 10:44:13 GMT

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;

Re: concatenate last observation variable value

Kurt_Bremser — Mon, 11 Dec 2017 10:49:34 GMT

@Rohit12 wrote:

I need this output through proc sql

Maxim 14.

Re: concatenate last observation variable value

Rohit12 — Mon, 11 Dec 2017 11:02:25 GMT

I need this thing through proc sql

Re: concatenate last observation variable value

Kurt_Bremser — Mon, 11 Dec 2017 11:06:42 GMT

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

Re: concatenate last observation variable value

RW9 — Mon, 11 Dec 2017 11:18:45 GMT

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.