Hi,
I would like to do the MYSQL of Groupconcat and also I would like to count the number of occurrences.
So far I got this:
data PLATFORMS;
input ORDERID $ PLATFORM $;
CARDS;
A ZXO
B ZXO
C EXP
D ZXO
D EXP
;
run;
data INVOLVED_PLATFORMS (drop=PLATFORM_old);
set PLATFORMS (rename=(PLATFORM=PLATFORM_old));
by ORDERID;
retain PLATFORM;
length PLATFORM $ 64;
if first.ORDERID then PLATFORM = '';
PLATFORM = catx(' ',trim(PLATFORM),PLATFORM_old);
if first.ORDERID then count = 0;
count = count + 1;
if last.ORDERID then output;
run;
I think I am halfway there, but I cant figure out why count is empty in the 4th row. It should be "2".
Thank you very much!
Dirk
@dirks wrote:
Hi,
I would like to do the MYSQL of Groupconcat and also I would like to count the number of occurrences.
What is that? Can you show an example of the output you'd like? In general, it's not a good idea to try and do 'exact' conversions, it's better to explain a problem and use a SAS type solution instead.
I have this:
A ZXO
B ZXO
C EXP
D ZXO
D EXP
E XXX
E YYY
E ZZZ
And I want this:
A "ZXO" 1
B "ZXO" 1
C "EXP" 1
D "ZXO EXP" 2
E "XXX YYY ZZZ" 3
I want to group by the 1st column, concat all strings of the 2nd column and count the records per 1st colum as a new 3rd column.
In SQL you would to this:
SELECT
Column1,
GROUPCONCAT(" ", Column2),
COUNT(Column1)
FROM
DATASET
GROUP BY Column1;
This has been proposed as an enhancement to SAS\SQL, see (and vote!) :
data PLATFORMS;
input ORDERID $ PLATFORM $;
CARDS;
A ZXO
B ZXO
C EXP
D ZXO
D EXP
;
run;
data want;
length list $ 200;
do n=1 by 1 until(last.ORDERID);
set PLATFORMS;
by ORDERID;
list=catx(' ',list,PLATFORM);
end;
list=cats('"',list,'"');
keep list n;
run;
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.