Load 2 Tables and process an input file against them

[srosenfe]

I need to load 2 tables, do a lookup in one(Table 2) and get a value from there, and look up that value up in Table 1.

Example:

Table 1 (key=12 bytes) ex. JOB00004 013 - has:

JOB00004 013

JOB00009 000

JOB00026 013

JOB00099 011

Table 2 (key=12 bytes - same as Table 1) has:

JOB00001 011 JOB00077

JOB00004 013 JOB00026

JOB00009 011 JOB00099

Read input file and do a "lookup" in Table 2 example

Input record1 = JOB00001 999 -  not found - put to error file

Input record2 = JOB00004 013 - record found in Table2 - now take JOB00026(field after 12 byte key) and do a lookup in Table 1 - JOB00026 Found in Table1

I am not clear on how to load these into 2 tables and do the lookups and handle the "not founds", etc...

I have read the "SAS coding by examples" by Ron Cody and still am unclear. Any help is appreciated.

Thanks

[Tom · Accepted solution]

How about something like this? 

data table1 ;

  input short $ 1-8 long $ 1-12 ;

cards;

JOB00004 013

JOB00009 000

JOB00026 013

JOB00099 011

run;

data table2 ;

  input long $1-12 short $14-21 ;

cards;

JOB00001 011 JOB00077

JOB00004 013 JOB00026

JOB00009 011 JOB00099

run;

data table3 ;

  input long $1-12 ;

cards;

JOB00001 999

JOB00004 013

run;

proc sql noprint ;

  create table want as

  select table3.long

       , table2.short

       , table1.long as long2

  from table3

  left join table2 on table3.long = table2.long

  left join table1 on table2.short = table1.short

  ;

quit;

proc print;

run;

Obs long         short         long2

1     JOB00001 999

2     JOB00004 013    JOB00026 JOB00026 013

[srosenfe]

Thanks Tom!