SAS 'BY' Process to List values producing unwanted duplicate values in list.

[OscarBoots1]

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 

[Reeza]

Change your logic to use BY SEX NAME, rather than just sex. 

Only add NAME to list if first.name

[OscarBoots1]

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;

[Kurt_Bremser · Accepted solution]

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;

[OscarBoots1]

Thanks KurtBremser!

That's got it.  :  )