Try to CONCAN string into a line. Oracle has group_concan(). How to do it in SAS/SQL? Anyone
proc sql;
create table summ_peek as
select case when cylinders>=6 then 1 else 0 end as flag, count(*) as cttot, concan(make) as make
from sashelp.cars
group by 1;
quit;
Here are two ways:
https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a
EDIT: That SQL will remerge with original table though, giving you 1 record per observation as the first calculation is row level, not an aggregate calculation.
ORACLE would error out I believe.
@hellohere wrote:
Try to CONCAN string into a line. Oracle has group_concan(). How to do it in SAS/SQL? Anyone
proc sql;
create table summ_peek as
select case when cylinders>=6 then 1 else 0 end as flag, count(*) as cttot, concan(make) as make
from sashelp.cars
group by 1;
quit;
Do you mind to Cut-&-Paste the webpage( text only is fine)? I cannot access that webpage. Thanks
*create sample data for demonstration;
data have;
infile cards dlm='09'x;
input OrgID Product $ States $;
cards;
1 football DC
1 football VA
1 football MD
2 football CA
3 football NV
3 football CA
;
run;
*Sort - required for both options;
proc sort data=have;
by orgID;
run;
**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
set have;
by orgID;
length combined $100.;
retain combined;
if first.orgID then
combined=states;
else
combined=catx(', ', combined, states);
if last.orgID then
output;
run;
**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
by orgID;
var states;
run;
data want_option2;
set wide;
length combined $100.;
combined=catx(', ', of state_:);
run;
The first solution suggested by @Reeza can be made a bit simpler (and slightly faster) like this:
data want_option1;
do until(last.orgID);
set have;
by orgID;
length combined $100.;
call catx(', ', combined, states);
end;
run;
And it has to be done in a data step, because SAS SQL does not have concatenation as a "summary" function, unlike most other SQL dialects.
If performance matters, you should always use CALL CATX rather than the function.
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.