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

Hi SAS Experts,

 

I am tryng to add multiple strings into one cell for distinct name. Attched are two files 1.raw data file 2.needed output file.

Is there is a way  to get the results in (2). needed output file using sas.

 

Thanks & Regards,

Sanjay.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
data raw;
    input name$ review$;
    datalines;
abc ghffh
abc fhgfh
efg gggh
efg fgfg
efg gff
efg ghgh
cgc thf
cgc tyg
;
run;

proc sort data=raw;
    by name;

data raw;
    set raw;
    by name;

    if first.name then
        count=1;
    else
        count+1;
    review=catt(count, ".", review);
run;

proc transpose data=raw out=wide prefix=rv_;
    by name;
    var review;
    id count;
run;

data want;
    set wide;
    review=catx("; ", of rv_:);
    drop _name_ rv_:;
run;

proc print data=want;
run;

View solution in original post

11 REPLIES 11
Reeza
Super User

I can't see the difference in CSV - may be my device. 

Please consider posting the requirements directly into the forum. 

sanjay1
Obsidian | Level 7

This is the raw file look like.

data raw;
input name$ review$;
datalines;
abc ghffh
abc fhgfh
efg gggh
efg fgfg
efg gff
efg ghgh
cgc thf
cgc tyg
;
run;

 

 

 

This is the needed output.

name  review

abc 1.ghffh; 2.fhgfh

efg  1.gggh; 2.fgfg; 3.gff; 4.ghgh

cgc  1.thf; 2.tyg

sschleede
Obsidian | Level 7

This is what I came up with. Comment out the keep statement and the if last.name statement if you want to see how it is building up s for each record.

 

proc sort data = raw;
	by name;
run;
data want;
	length s $ 100;
	set raw;
	by name;
	retain s i;
	if first.name then do;
		i = 1;
		s = catx(' ',name,cats(i,'.',review));
	end;
	else do;
		i = i + 1;
		s = catx('; ',s,cats(i,'.',review));
	end;
	if last.name;
	keep s;

run;
proc print data = want;
run;

 

 

sanjay1
Obsidian | Level 7

Hi sschleede,

 

Its working fine,but i want name as separate variable and all the reviews concatenated into one cell. 

Reeza
Super User

I'm confused, isn't that exactly what the code does? Please explain in detail. 

 


@sanjay1 wrote:

Hi sschleede,

 

Its working fine,but i want name as separate variable and all the reviews concatenated into one cell. 


 

ballardw
Super User

@sanjay1 wrote:

Hi sschleede,

 

Its working fine,but i want name as separate variable and all the reviews concatenated into one cell. 


Show the actual results your a getting if that is not the case. Perhaps your data isn't actually as you posted for the example?

Reeza
Super User

Another method is to transpose to a wide file via PROC TRANSPOSE and then use CATX on the new variable with an array. 

sschleede
Obsidian | Level 7

The code I posted put the result in the variable s. Below puts the result back in the variable review.

I don't know how to copy SAS output into this post, but you can run the code below freestanding. I think the output looks like what was in the original post.

 

data raw;
input name$ review$;
datalines;
abc ghffh
abc fhgfh
efg gggh
efg fgfg
efg gff
efg ghgh
cgc thf
cgc tyg
;
run;

proc sort data = raw;
	by name;
run;
data want;
	length review $ 100;
	set raw (rename = (review = r));
	by name;
	retain review i;
	if first.name then do;
		i = 1;
		review = catx(' ',name,cats(i,'.',r));
	end;
	else do;
		i = i + 1;
		review = catx('; ',review,cats(i,'.',r));
	end;
	if last.name;
	keep name review;

run;
proc print data = want;
var name review;
run;
sschleede
Obsidian | Level 7

OK. I reread the request. I was reading the output as 'abc 1.ghffh; 2. fhgfh' instead of 'abc' and '1.ghffh; 2. fhgfh'.

Small change to the code.

 

data raw;
input name$ review$;
datalines;
abc ghffh
abc fhgfh
efg gggh
efg fgfg
efg gff
efg ghgh
cgc thf
cgc tyg
;
run;

proc sort data = raw;
	by name;
run;
data want;
	length review $ 100;
	set raw (rename = (review = r));
	by name;
	retain review i;
	if first.name then do;
		i = 1;
		review = cats(i,'.',r);
	end;
	else do;
		i = i + 1;
		review = catx('; ',review,cats(i,'.',r));
	end;
	if last.name;
	keep name review;

run;
proc print data = want;
var name review;
run;
Reeza
Super User
data raw;
    input name$ review$;
    datalines;
abc ghffh
abc fhgfh
efg gggh
efg fgfg
efg gff
efg ghgh
cgc thf
cgc tyg
;
run;

proc sort data=raw;
    by name;

data raw;
    set raw;
    by name;

    if first.name then
        count=1;
    else
        count+1;
    review=catt(count, ".", review);
run;

proc transpose data=raw out=wide prefix=rv_;
    by name;
    var review;
    id count;
run;

data want;
    set wide;
    review=catx("; ", of rv_:);
    drop _name_ rv_:;
run;

proc print data=want;
run;
sanjay1
Obsidian | Level 7

Thank you It worked for me

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
  • 11 replies
  • 2144 views
  • 4 likes
  • 4 in conversation