topic Re: Concatenating strings in SAS Programming

Concatenating strings

sanjay1 — Thu, 25 May 2017 14:57:05 GMT

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.

Re: Concatenating strings

Reeza — Thu, 25 May 2017 14:58:34 GMT

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

Please consider posting the requirements directly into the forum.

Re: Concatenating strings

sanjay1 — Thu, 25 May 2017 15:14:32 GMT

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

Re: Concatenating strings

sschleede — Thu, 25 May 2017 15:33:06 GMT

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;

Re: Concatenating strings

Reeza — Thu, 25 May 2017 15:30:02 GMT

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

Re: Concatenating strings

sanjay1 — Thu, 25 May 2017 16:12:00 GMT

Hi sschleede,

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

Re: Concatenating strings

Reeza — Thu, 25 May 2017 16:19:51 GMT

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.

Re: Concatenating strings

ballardw — Thu, 25 May 2017 16:35:38 GMT

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

Re: Concatenating strings

sschleede — Thu, 25 May 2017 17:20:29 GMT

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;

Re: Concatenating strings

sschleede — Thu, 25 May 2017 17:27:41 GMT

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;

Re: Concatenating strings

Reeza — Thu, 25 May 2017 17:35:30 GMT

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;

Re: Concatenating strings

sanjay1 — Fri, 26 May 2017 11:09:16 GMT

Thank you It worked for me