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
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.
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.
Hi Reeza,
I've attached a quick example.
Thanks
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
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
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.
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.
Should the LIST variable be RETAINED?
@ballardw Yes...
Thanks Reeza!
Thanks for translating that for me.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.