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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.