topic Re: How to loop into a lookup table in SAS Programming

How to loop into a lookup table

lizzy28 — Thu, 02 Dec 2021 14:45:39 GMT

Hi all,

I need to pull the claims data with 20 procedure codes, and all the procedure codes are stored in a procedure code lookup table. Is there a simpler way to do that?

My code:

proc sql;
create table claims_data as
select distinct a.patient_id
,a.claim_num
,a.service_dt
,pr1.proc_1_cd
,pr2.proc_2_cd
,pr3.proc_3_cd

...

,pr20.proc_20_cd
from claim_file a
left join proc_lookup on pr1 on a.proc_1_cd_id=pr1.proc_cd_id
left join proc_lookup on pr2 on a.proc_2_cd_id=pr2.proc_cd_id
left join proc_lookup on pr3 on a.proc_3_cd_id=pr3.proc_cd_id

...
;quit;

In the procedure code lookup table, there are fields like this:

Field_num	Field_Name
1	PROC_CD_ID
2	PROC_CD_CD
3	PROC_CD_DESC
4	PROC_CD_TYPE

PROC_CD_ID is the primary key.

Thank you all!

Lizi

Re: How to loop into a lookup table

Reeza — Thu, 02 Dec 2021 14:55:48 GMT

Maybe a format? What information are you pulling from the procedure code table? The Description/Type?
Can you show the full query - specifically the sections that pull data from the lookup table.

Re: How to loop into a lookup table

lizzy28 — Thu, 02 Dec 2021 15:25:45 GMT

I just need to pull all the procedure codes which are not available in the claims file. But procedure code ids are available in the claims file.

Here is the full query:
proc sql;
create table claims_data as
select distinct a.patient_id
,a.claim_num
,a.service_dt
,pr1.proc_1_cd
,pr2.proc_2_cd
,pr3.proc_3_cd
,pr4.proc_4_cd
,pr5.proc_5_cd
,pr6.proc_6_cd
,pr7.proc_7_cd
,pr8.proc_8_cd
,pr9.proc_9_cd
,pr10.proc_10_cd
,pr11.proc_11_cd
,pr12.proc_12_cd
,pr13.proc_13_cd
,pr14.proc_14_cd
,pr15.proc_15_cd
,pr16.proc_16_cd
,pr17.proc_17_cd
,pr18.proc_18_cd
,pr19.proc_19_cd
,pr20.proc_20_cd
from claim_file a
left join proc_lookup pr1 on a.proc_1_cd_id=pr1.proc_cd_id
left join proc_lookup pr2 on a.proc_2_cd_id=pr2.proc_cd_id
left join proc_lookup pr3 on a.proc_3_cd_id=pr3.proc_cd_id
left join proc_lookup pr4 on a.proc_4_cd_id=pr4.proc_cd_id
left join proc_lookup pr5 on a.proc_5_cd_id=pr5.proc_cd_id
left join proc_lookup pr6 on a.proc_6_cd_id=pr6.proc_cd_id
left join proc_lookup pr7 on a.proc_7_cd_id=pr7.proc_cd_id
left join proc_lookup pr8 on a.proc_8_cd_id=pr8.proc_cd_id
left join proc_lookup pr9 on a.proc_9_cd_id=pr9.proc_cd_id
left join proc_lookup pr10 on a.proc_10_cd_id=pr10.proc_cd_id
left join proc_lookup pr11 on a.proc_11_cd_id=pr11.proc_cd_id
left join proc_lookup pr12 on a.proc_12_cd_id=pr12.proc_cd_id
left join proc_lookup pr13 on a.proc_13_cd_id=pr13.proc_cd_id
left join proc_lookup pr14 on a.proc_14_cd_id=pr14.proc_cd_id
left join proc_lookup pr15 on a.proc_15_cd_id=pr15.proc_cd_id
left join proc_lookup pr16 on a.proc_16_cd_id=pr16.proc_cd_id
left join proc_lookup pr17 on a.proc_17_cd_id=pr17.proc_cd_id
left join proc_lookup pr18 on a.proc_18_cd_id=pr18.proc_cd_id
left join proc_lookup pr19 on a.proc_19_cd_id=pr19.proc_cd_id
left join proc_lookup pr20 on a.proc_20_cd_id=pr20.proc_cd_id
;quit;

Re: How to loop into a lookup table

Reeza — Thu, 02 Dec 2021 15:28:24 GMT

So you're looking for codes in the second table that are not anywhere in the claims table?

Re: How to loop into a lookup table

Kurt_Bremser — Thu, 02 Dec 2021 15:30:25 GMT

Either create a format from the proc_lookup table, or read it into a hash object and use arrays and a DO loop to do the lookup.

Example code:

data proc_lookup;
input proc_cd_id proc_cd_cd $;
datalines;
1 x
2 y
3 z
;

data have;
input proc_1_cd_id proc_2_cd_id;
datalines;
1 1
2 3
1 2
;

data want;
set have;
array proc_id {2} proc_1_cd_id proc_2_cd_id;
array codes {2} $ proc_1_cd_cd proc_2_cd_cd;
if _n_ = 1
then do;
  length proc_cd_id 8 proc_cd_cd $8;
  declare hash l (dataset:"proc_lookup");
  l.definekey("proc_cd_id");
  l.definedata("proc_cd_cd");
  l.definedone();
end;
do i = 1 to 2;
  proc_cd_id = proc_id{i};
  if l.find() = 0 then codes{i} = proc_cd_cd;
end;
drop i;
run;

Re: How to loop into a lookup table

lizzy28 — Thu, 02 Dec 2021 15:38:18 GMT

Thank you, Kurt! But I need to use PROC SQL to pull data from a database. So data step doesn't work.

Re: How to loop into a lookup table

lizzy28 — Thu, 02 Dec 2021 15:41:41 GMT

But procedure code ids are available in both files to join.

Re: How to loop into a lookup table

Reeza — Thu, 02 Dec 2021 15:59:38 GMT

Instead of code, perhaps show us a small data sample, using say 5 codes and a short lookup table and show us what you need to accomplish will be more helpful.

Re: How to loop into a lookup table

lizzy28 — Thu, 02 Dec 2021 20:50:21 GMT

Here is a quick example of the lookup table:

PROC_CD_ID	PROC_CD	PROC_CD_DESC	PROC_CD_TYPE
7096	86376	MICROSOMAL ANTIBODIES, EACH	C
7098	86382	NEUTRALIZATION TEST, VIRAL	C
7099	86384	NITROBLUE TETRAZOLIUM DYE TE	C
7100	86403	PARTICLE AGGLUTINATION; SCRE	C
7102	86430	RHEUMATOID FACTOR; QUALITATI	C

The first field PROC_CD_ID is available in both the claim file and the lookup table while the second field PROC_CD is only available in the lookup table. I need to pull the field PROC_CD through joining PROC_CD_ID in both tables.

Thank you!

Re: How to loop into a lookup table

Reeza — Thu, 02 Dec 2021 20:58:22 GMT

That's one part. You need to show the full picture, which means an example (Fake data is fine) of the main table and most importantly - what you want as output. The output part, what are you asking about is the part that's unclear.

Re: How to loop into a lookup table

SASKiwi — Thu, 02 Dec 2021 23:01:16 GMT

What database is that? A simpler approach would be for your DBA to create a view combining the 20 lookup tables, then you do one join to the view.

Re: How to loop into a lookup table

Kurt_Bremser — Fri, 03 Dec 2021 09:05:49 GMT

You are working with SAS, so the data step is available.

If, OTOH, this is to be done directly on the DB via explicit passthrough, consult the DBA's, or a community for that particular DB environment.

Re: How to loop into a lookup table

Reeza — Fri, 03 Dec 2021 17:19:30 GMT

data have;
infile cards dlm=',';
informat ID $1. proc_1_cd proc_2_cd proc_3_cd proc_4_cd proc_5_cd $8.;
input ID $ proc_1_cd proc_2_cd proc_3_cd proc_4_cd proc_5_cd ;
cards;
A, 123.43, 124.34, 134.35, 124.89, 343.43
B, 423.43, 456.86, 129, 124.89, 145
C, 123.43, 124.34, 134.35, 124.89, 285.95
D, 854.70, 874.6,  68.55, 294.1, 4.5
E, 123.43, 124.34C, 134.35B, 124.89, 89.65A
;;;;
run;


proc sql;
create table distinct_codes as
select distinct proc_1_cd from have
union 
select distinct proc_2_cd from have
union 
select distinct proc_3_cd from have
union 
select distinct proc_4_cd from have
union 
select distinct proc_5_cd from have;
quit;



proc sql;
create table codes_not_found as
select * from lookup where proc_cd not in (select distinct proc_code from distinct_codes);
quit;