BookmarkSubscribeRSS Feed
Marilyn
Calcite | Level 5
Can someone help me build a complex query?
I want to go thru all inpatient(ABC files) and outpatient (DEF files) files that we have. They are stored with same name and then FYNN. I need 10 years worth.
So I need it to go thrru each inpatient file by year and see if the ssn matches one in a file of 200k numbers. If it matches, then I want it to look thru a group of 10 diagnoses and exclude any records with dx 345, 346, or 347.
I want to end up with all the records that match the ssn's I was looking for that do NOt have the excluded diagnoses.
So how to do the looping for the years and possibly an array of the diagnoses...?
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Some ideas for SAS programming techniques:

1) use a SAS format for your look-up on SSN values.
2) you can use the INTNX and INTCK functions in the SAS DATA step to increment (loop) through the time-period dates (year) - a DO / END loop could work, depending on your SAS data variable values.
3) if you need it, you can pre-load an array with constant values, and use a DATA step code construct to find a match, like below:

DO I=1 TO DIM();
* your match-test-logic goes here. ;
END;


Suggest you post a small example of your input data, code up a SAS DATA step to read the DATALINES (sample input), creating a SAS file, then begin putting together your overall program, one small component at a time, and then post a reply to the forum subscribers for feedback and guidance.


Scott Barry
SBBWorks, Inc.
Doc_Duke
Rhodochrosite | Level 12
Marilyn,

To follow-up on Scott's comments, Rick Langston has a paper that works with look-up techniques:

http://support.sas.com/resources/papers/proceedings09/037-2009.pdf

It looks like you just have 20 files to examine. It may be easier to hard-code the concatenation than to build the looping code.

The ARRAY approach is the easiest way to go through the 10 diagnosis coded. In the loop, set a flag for the exclusion; outside the loop, examine the flag to decide to output the record or not.

Your expressed request, and this approach, will NOT exclude persons with these diagnoses on ANY encounter. To do that, output an ID list of the subset with the diagnosis on any encounter and re-merge that with the original set of hits. That is an easy SQL code:

SELECT * FROM keepers
WHERE ssn NOT IN (SELECT DISTINCT ssn FROM excludes)

Doc Muhlbaier
Duke
Marilyn
Calcite | Level 5
Thanks
I am trying to make the code as efficient as possible too, which is why I thought it might be better to loop through the years and accumulate everything at the end.
I am hoping that I can create a model that we can use repetitvely as well.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
As suggested previously, explore using a DO / END loop to output SAS (numeric) DATE variables, using the INTNX function - see the SAS support http://support.sas.com/ website supplemental technical paper (link below) on this topic.

Scott Barry
SBBWorks, Inc.


SAS Dates, Times, and Interval Functions
Definitions and Explanations
SAS Dates, Times, and Datetimes:
http://support.sas.com/techsup/technote/ts668.pdf
deleted_user
Not applicable
I had to do something similar a while ago. You can probably tweek the following to your needs.

%macro mem_detail_tin_mon;

proc sql noprint;
select distinct tin into :tin1 - :tin48 from req.tins;
quit;

proc sql noprint;
select distinct monyr into :mon1 - :mon20 from monthlist;
quit;

%do i = 1 %to 48;
%do j = 1 %to 20;
data Final;
set req.mem_detail_2;

where tin = "&&tin&i" and monyr = "&&mon&j";
run;


proc export data = Final
outtable = "PD_&&tin&i.._&&mon&j.."
dbms = access2000 replace;
database = "X:Req_detail_32808.mdb";
run;

%end;
%end;

%mend mem_detail_tin_mon;

%mem_detail_tin_mon;

sas-innovate-2024.png

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.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 5 replies
  • 1342 views
  • 0 likes
  • 4 in conversation