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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.