How to loop into a lookup table

[lizzy28]

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

[Reeza]

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.

[lizzy28]

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;

[Reeza]

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

[lizzy28]

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

[Reeza]

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.

[lizzy28]

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!

[Reeza]

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.

[Reeza]

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;

[Kurt_Bremser]

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;

[lizzy28]

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

[SASKiwi]

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.

[Kurt_Bremser]

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.