topic Re: Load 2 Tables and process an input file against them in SAS Procedures

Load 2 Tables and process an input file against them

srosenfe — Thu, 04 Sep 2014 18:31:15 GMT

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

Re: Load 2 Tables and process an input file against them

Tom — Fri, 05 Sep 2014 00:17:07 GMT

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

Re: Load 2 Tables and process an input file against them

srosenfe — Mon, 08 Sep 2014 12:24:31 GMT

Thanks Tom!