BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rohit12
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20
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
Obsidian | Level 7

I need this thing through proc sql 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1334 views
  • 1 like
  • 4 in conversation