DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

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

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 


Accepted Solutions
Solution
‎08-06-2016 05:20 AM
Super User
Posts: 6,938

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 17,829

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

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

 

Only add NAME to list if first.name

Contributor
Posts: 54

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

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;

 

Solution
‎08-06-2016 05:20 AM
Super User
Posts: 6,938

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 54

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

Thanks KurtBremser!

 

That's got it.  :  )

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 282 views
  • 3 likes
  • 3 in conversation