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
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
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
Thanks Tom!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.