DATA Step, Macro, Functions and more

SAS Version of Oracle's LISTAGG Function

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

SAS Version of Oracle's LISTAGG Function

Hi,

 

I have a field that gives several results per Customer & I want to list those results separated by a Comma.

 

In Oracle, there is a Function called 'LISTAGG' & I have heard about the 'BY' process in SAS but I'm not sure how to use it.

 

Does anyone have any clear examples of this 'BY' process?

 

Thanks


Accepted Solutions
Solution
‎07-27-2016 06:48 PM
Super User
Posts: 19,770

Re: SAS Version of Oracle's LISTAGG Function

[ Edited ]
Posted in reply to OscarBoots1
Proc sort data= have;
By custId;
Run;

Data want;

Set have;
By custID;

Length list $500.;
Retain List; If first.custID then list= variable; Else list = catx(', ', list, variable); If last.custID then output; Run;

Replace with your dataset name and appropriate variable names. 

View solution in original post


All Replies
Super User
Posts: 19,770

Re: SAS Version of Oracle's LISTAGG Function

Posted in reply to OscarBoots1

Can you post an example of what you have and what you want?

 

I'm not sure there's a direct equivalent but there definitely is a solution.

Contributor
Posts: 72

Re: SAS Version of Oracle's LISTAGG Function

Hi Reeza,

 

I've attached a quick example.

 

Thanks

Super User
Posts: 19,770

Re: SAS Version of Oracle's LISTAGG Function

Posted in reply to OscarBoots1

Two ways I can recommend: 

1. use proc transpose to change data from long to wide and then use CATX in a datastep 

2. Sort first and then use a data step

 

One example is below

https://communities.sas.com/t5/Base-SAS-Programming/Combining-observations/m-p/184934/highlight/true...

 

 

Contributor
Posts: 72

Re: SAS Version of Oracle's LISTAGG Function

Thanks Reeza,

 

I wonder if you had come across this solution before?

 

Data WANT;
>Set HAVE;
>length list $32767.;
>
>by cpevent ptm; 
>
>if first.ptm then list = ptno;
>else list = cats(list, ',' ptno);
>
>if last.ptm then output;
>run;

I got it from this link;

http://marc.info/?l=sas-l&m=134440723920631&w=3

 

I'm keen on finding out how to apply it and what exactly does  'ptm' & 'ptno' mean?

 

Thanks 

Super User
Posts: 19,770

Re: SAS Version of Oracle's LISTAGG Function

Posted in reply to OscarBoots1

Yes I've seen that solution. This is the data step solution, the second option I suggested. 

 

Ptm and and ptno in this case are specific variables in their dataset. You would customize it to your data. 

 

You put the GROUP by variables in the BY statement. 

 

I would recomment CATX instead of CATS function.

 

FIRST/LAST processing allows conditional execution at the first occurrence of each group and last. 

 

Your first/last should reference the last variable in your BY statement. 

Solution
‎07-27-2016 06:48 PM
Super User
Posts: 19,770

Re: SAS Version of Oracle's LISTAGG Function

[ Edited ]
Posted in reply to OscarBoots1
Proc sort data= have;
By custId;
Run;

Data want;

Set have;
By custID;

Length list $500.;
Retain List; If first.custID then list= variable; Else list = catx(', ', list, variable); If last.custID then output; Run;

Replace with your dataset name and appropriate variable names. 

Super User
Posts: 11,343

Re: SAS Version of Oracle's LISTAGG Function

Should the LIST variable be RETAINED?

Super User
Posts: 19,770

Re: SAS Version of Oracle's LISTAGG Function

@ballardw Yes...Cat Embarassed

Contributor
Posts: 72

Re: SAS Version of Oracle's LISTAGG Function

Thanks Reeza!

 

Thanks for translating that for me. Smiley Wink

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 1678 views
  • 4 likes
  • 3 in conversation