Help using Base SAS procedures

Load 2 Tables and process an input file against them

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Load 2 Tables and process an input file against them

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


Accepted Solutions
Solution
‎09-04-2014 08:17 PM
Super User
Super User
Posts: 7,050

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

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

View solution in original post


All Replies
Solution
‎09-04-2014 08:17 PM
Super User
Super User
Posts: 7,050

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

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

Contributor
Posts: 23

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

Thanks Tom!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 180 views
  • 0 likes
  • 2 in conversation