DATA Step, Macro, Functions and more

Group Concat and Counting

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Group Concat and Counting

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


Accepted Solutions
Solution
‎11-30-2017 04:42 AM
Regular Contributor
Posts: 226

Re: Group Concat and Counting

Retaining count could help.

View solution in original post


All Replies
Solution
‎11-30-2017 04:42 AM
Regular Contributor
Posts: 226

Re: Group Concat and Counting

Retaining count could help.
Super User
Posts: 24,004

Re: Group Concat and Counting


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. 

Contributor
Posts: 50

Re: Group Concat and Counting

[ Edited ]

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;
Esteemed Advisor
Posts: 5,624

Re: Group Concat and Counting

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

PG
Super User
Posts: 10,849

Re: Group Concat and Counting

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;

Contributor
Posts: 50

Re: Group Concat and Counting

Thank you very much. I will use this one for future projects.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 319 views
  • 3 likes
  • 5 in conversation