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.
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;
I can't see the difference in CSV - may be my device.
Please consider posting the requirements directly into the forum.
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
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;
Hi sschleede,
Its working fine,but i want name as separate variable and all the reviews concatenated into one cell.
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.
@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?
Another method is to transpose to a wide file via PROC TRANSPOSE and then use CATX on the new variable with an array.
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;
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;
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;
Thank you It worked for me
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.