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
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.
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;
I need this thing through proc sql
Not using the right tool is plain stupid. @PeterClemmensen's suggestion aptly demonstrates the power of the data step for such a task.
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.
@Rohit12 wrote:
I need this output through proc sql
Maxim 14.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.