BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dirks
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
error_prone
Barite | Level 11
Retaining count could help.

View solution in original post

6 REPLIES 6
error_prone
Barite | Level 11
Retaining count could help.
Reeza
Super User

@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
Quartz | Level 8

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
Opal | Level 21

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
Ksharp
Super User

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
Quartz | Level 8
Thank you very much. I will use this one for future projects.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4077 views
  • 4 likes
  • 5 in conversation