Concatenating strings

[sanjay1]

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.

[Reeza]

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

Please consider posting the requirements directly into the forum. 

[sanjay1]

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]

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]

Hi sschleede,

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

[Reeza]

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]

---

@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]

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

[sschleede]

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]

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 · Accepted solution]

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]

Thank you It worked for me