DATA Step, Macro, Functions and more

Concatenating strings

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 114
Accepted Solution

Concatenating strings

[ Edited ]

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.

Attachment
Attachment

Accepted Solutions
Solution
‎05-26-2017 06:50 AM
Super User
Posts: 17,771

Re: Concatenating strings

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


All Replies
Super User
Posts: 17,771

Re: Concatenating strings

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

Please consider posting the requirements directly into the forum. 

Frequent Contributor
Posts: 114

Re: Concatenating strings

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

Contributor
Posts: 22

Re: Concatenating strings

[ Edited ]

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;

 

 

Frequent Contributor
Posts: 114

Re: Concatenating strings

Hi sschleede,

 

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

Super User
Posts: 17,771

Re: Concatenating strings

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. 


 

Super User
Posts: 10,474

Re: Concatenating strings


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?

Super User
Posts: 17,771

Re: Concatenating strings

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

Contributor
Posts: 22

Re: Concatenating strings

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;
Contributor
Posts: 22

Re: Concatenating strings

[ Edited ]

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;
Solution
‎05-26-2017 06:50 AM
Super User
Posts: 17,771

Re: Concatenating strings

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;
Frequent Contributor
Posts: 114

Re: Concatenating strings

Thank you It worked for me

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 309 views
  • 4 likes
  • 4 in conversation