topic Re: SAS 'BY' Process to List values producing unwanted duplicate values in list. in SAS Programming

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

OscarBoots1 — Thu, 04 Aug 2016 04:21:04 GMT

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

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

Reeza — Thu, 04 Aug 2016 04:41:47 GMT

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

Only add NAME to list if first.name

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

OscarBoots1 — Thu, 04 Aug 2016 05:07:13 GMT

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;

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

Kurt_Bremser — Thu, 04 Aug 2016 05:29:10 GMT

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;

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

OscarBoots1 — Sat, 06 Aug 2016 09:19:59 GMT

Thanks KurtBremser!

That's got it. : )