Group Concat and Counting

[dirks]

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

[error_prone · Accepted solution]

Retaining count could help.

[Reeza]

---

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

[dirks]

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;

[PGStats]

This has been proposed as an enhancement to SAS\SQL, see (and vote!) :

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-GROUP-CONCAT-function-to-proc-sql/idi-p/3230...

[Ksharp]

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;

[dirks]

Thank you very much. I will use this one for future projects.