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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 1129 views
  • 1 like
  • 4 in conversation