Hi Forum,
I recently got some help with using the 'BY' Process to list a Columns results if there were more than one.
here's the script;
data CLASS (index=(SEX)); set SASHELP.CLASS; run; data T; length NAMES $200; retain NAMES; set CLASS; by SEX; if first.SEX then NAMES=''; NAMES=catx(',',NAMES, NAME) ; if last.SEX then putlog NAMES=; run;
I'm finding that this allows duplicate values & I wondered if there was a way to just collect a list of Distinct values.
I have tried using distinct but it's not working.
Any suggestions?
Thanks
Add an additional first.:
proc sort data=database_1;
by member_id drc_type;
run;
data drc_type_list;
set database_1;
by member_id drc_type;
length list $500.;
retain list;
if first.member_id then list = " ";
if first.drc_type then list = catx(' | ', trim(list), drc_type);
if last.member_id then output;
run;
Change your logic to use BY SEX NAME, rather than just sex.
Only add NAME to list if first.name
Thanks Reeza,
I thought I'd give you the script I'm using as I can't adapt that one to do what you've advised.
I've added 'DRC_TYPE ' to the 'BY' Process as well as 'MEMBER_ID'.
Only I'm still getting duplicates??
Proc sort data= DATABASE_1; By MEMBER_ID DRC_TYPE; Run; Data DRC_TYPE_LIST; Set DATABASE_1; By MEMBER_ID DRC_TYPE; Length list $500.;Retain List; If first.MEMBER_ID then list= DRC_TYPE; Else list = catx(' | ', list, DRC_TYPE); If last.MEMBER_ID then output; Run;
Add an additional first.:
proc sort data=database_1;
by member_id drc_type;
run;
data drc_type_list;
set database_1;
by member_id drc_type;
length list $500.;
retain list;
if first.member_id then list = " ";
if first.drc_type then list = catx(' | ', trim(list), drc_type);
if last.member_id then output;
run;
Thanks KurtBremser!
That's got it. : )
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.
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.