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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.