Desktop productivity for business analysts and programmers

Excluding codes from data sets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Excluding codes from data sets

 

 

This is what I have so far:

 

Excludes antipsychotic meds (Long-Acting Injections) dispensed:

DATA CLMSPULL_NEW2;

     SET CLMSPULL_NEW;

 NOT IN LIST &LAINJ;

 

Excludes hospice codes:

DATA CLMSPULL_NEW3;

     SET CLMSPULL_NEW2;

NOT IN LIST &HOSPICE;

 

These are the codes built into macros:

 

/Exclusions/

%LET HOSPICE = ('99377','99378','G0182','G9473','G9474','G9475','G9476', 'G9477','G9478','G9479','Q5003','Q5004','Q5005','Q5006','Q5007','Q5008','Q5010','S9126','T2042', 'T2043','T2044','T2045','T2046','0143', '0144', '0149', '0150', '0151', '0152', '0153', '0154', '0159', '0160', '0164', '0167', '0169', '0200', '0201', '0202', '0203', '0204', '0206', '0207', '0208', '0209', '0210', '0211', '0212', '0213', '0214', '0219', '0720', '0721', '0722', '0723', '0724', '0729', '0987', '99221','99222','99223','99231','99232','99233',
'99238','99239','99251','99252','99253','99254','99255','99291');

/*Long-Acting Injections*/

%LET LAINJ = ('J0401','J1631','J2358','J2426','J2680','J2794');


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,401

Re: Excluding codes from data sets

I can't read code which is SHOUTING at me.  Please stop coding all in uppercase and apply some code formatting to your coding efforts, this is more important than any code you will write.

 

Now then, you have not stated what your problem is?  Your code is not syntactically correct.  

data clmspull_new2;
  set clmspull_new;
  where <variable> not in &lainj.;
run;

<variable> should be replaced with the variable in question, which you have not provided.  This will work and only select records in the macro list.

 

Finally, macro is not the place to be handling data.  How do you maintain that list of values?  Seems a real pain.  Store data in datasets, and use Base SAS - the actual programming language - to process that data simply, easily and far more robust.  For instance, if you put those codes in one dataset with a subgroup e.g:

TYPE         CODE

HOSPICE 99377

...

LAINJ       J0401

...

 

Within this you can either use the dataset as is (by use of merge or:

proc sql;
  create table want as
  select *
  from   have
  where code in (select code from codelist where type="HOSPICE");
quit;

The dataset is far simpler to work with, remove duplicates etc.

Or you could use that dataset to create a format list - very useful in certain circumstances.  Far more robust than putting data in macro.

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Super User
Posts: 9,401

Re: Excluding codes from data sets

I can't read code which is SHOUTING at me.  Please stop coding all in uppercase and apply some code formatting to your coding efforts, this is more important than any code you will write.

 

Now then, you have not stated what your problem is?  Your code is not syntactically correct.  

data clmspull_new2;
  set clmspull_new;
  where <variable> not in &lainj.;
run;

<variable> should be replaced with the variable in question, which you have not provided.  This will work and only select records in the macro list.

 

Finally, macro is not the place to be handling data.  How do you maintain that list of values?  Seems a real pain.  Store data in datasets, and use Base SAS - the actual programming language - to process that data simply, easily and far more robust.  For instance, if you put those codes in one dataset with a subgroup e.g:

TYPE         CODE

HOSPICE 99377

...

LAINJ       J0401

...

 

Within this you can either use the dataset as is (by use of merge or:

proc sql;
  create table want as
  select *
  from   have
  where code in (select code from codelist where type="HOSPICE");
quit;

The dataset is far simpler to work with, remove duplicates etc.

Or you could use that dataset to create a format list - very useful in certain circumstances.  Far more robust than putting data in macro.

PROC Star
Posts: 1,288

Re: Excluding codes from data sets

You're very close.

 

Here's a worked example

 

%let AgeList = (12, 13, 14);

data Want;
	set sashelp.class;

	if Age not in &AgeList.;
run;

 

Tom

 

Frequent Contributor
Posts: 104

Re: Excluding codes from data sets

Thank you as well!!
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 92 views
  • 2 likes
  • 3 in conversation