SAS Version of Oracle's LISTAGG Function

[OscarBoots1]

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

[Reeza]

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.

[OscarBoots1]

Hi Reeza,

I've attached a quick example.

Thanks

[Reeza]

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

[OscarBoots1]

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 

[Reeza]

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. 

[Reeza · Accepted solution]

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. 

[ballardw]

Should the LIST variable be RETAINED?

[Reeza]

@ballardw Yes...

[OscarBoots1]

Thanks Reeza!

Thanks for translating that for me.