topic Joins in New SAS User

Joins

Sandeep77 — Fri, 27 May 2022 12:46:43 GMT

Hi Experts,

I have imported a file in SAS and now adding few more details in that file but I am getting the below error. I feel it's a issue in joining. Please suggest

Error Log:

29         proc sql;
30           create table Manual_tracing as
31         
32         select a.*,
33         
34         	   	       b.debt_code,
35         			   b.tran_code,
36                        b.tx_date,
37                        b.tx_time,
38         			   b.dt_tx_date,
39                        b.tx_manauto,
40                        b.op_code,
41                        b.tx_char_i11,
42                        c.td_altdescrn,
43         			   c.td_stddescrn
44         
45                 from p2scflow.debt_trans as b
46         		inner join b on a.debt_code = b.debt_code
47                 inner join p2scflow.transdescrn as c
48                 on b.Tran_code = c.Tran_code
49                 where  b.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748')
50         
51         	order by a.debt_code
52         ;
ERROR: File WORK.B.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
2                                                          The SAS System                               13:44 Thursday, May 26, 2022

53         quit;

Proc import 
datafile='\\test.local\shares\public\Full_manual_trace_book.csv'
out=Manual_trace_full
dbms=csv
replace;
run;


proc sql;
  create table Manual_tracing as
select a.*,
 b.debt_code,
b.tran_code,
 c.td_altdescrn,
  c.td_stddescrn
             
        from p2scflow.debt_trans as b
		inner join b on a.debt_code = b.debt_code
        inner join p2scflow.transdescrn as c
        on b.Tran_code = c.Tran_code
        where  b.tran_code in ('SO971','SO972') 
	order by b.debt_code;
quit;

Re: Joins

ballardw — Fri, 27 May 2022 14:49:57 GMT

Please examine:

45                 from p2scflow.debt_trans as b
46         		inner join b on a.debt_code = b.debt_code

If you meant to use Pwscflow.debt_trans the join then you do not list the alias b in that position. What follows a JOIN should be another data source.You syntax says to look for a data set named B which defaults to the Work library

Since you do not define the alias A anywhere one suspects a couple of typos.

To properly write this join to a different data set something like

45                 from p2scflow.debt_trans as b
46         		inner join
                        lib.datasetname as a 
                        on a.debt_code = b.debt_code

Or possibly you meant the P2scflow.debt_trans to be aliased as A and missed the data set name for the alias B.

The syntax does not require the AS but I use it to make sure my intent of creating an alias is obvious.

Re: Joins

Sandeep77 — Fri, 27 May 2022 15:11:34 GMT

I tried making those changes

Proc import 
datafile='\\test.local\shares\public\Full_manual_trace_book.csv'
out=Manual_trace_full
dbms=csv
replace;
run;

proc sql;
  create table Manual_tracing as
select a.*,
 b.debt_code,
 b.tran_code,
 c.td_altdescrn,
 c.td_stddescrn        
        from p2scflow.debt_trans as b
		inner join manual_trace_full as a on a.debt_code = b.debt_code
        inner join p2scflow.transdescrn as c
        on b.Tran_code = c.Tran_code
        where  b.tran_code in ('SO971','SO972') 
order by b.debt_code;
quit;

I am getting this error now:

29         proc sql;
30           create table Manual_tracing as
31         
32         select a.*,
34         	   	b.debt_code,
35         		b.tran_code,
42                        c.td_altdescrn,
43         		  c.td_stddescrn
44         
45                 from p2scflow.debt_trans as b
46         		inner join manual_trace_full as a on a.debt_code = b.debt_code
47                 inner join p2scflow.transdescrn as c
48                 on b.Tran_code = c.Tran_code
49                 where  b.tran_code in ('SO971','SO972')
50         
51         
52         ;
ERROR: Expression using equals (=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
2                                                          The SAS System                               13:44 Thursday, May 26, 2022

53         quit;

Re: Joins

ballardw — Fri, 27 May 2022 15:44:37 GMT

That means that the variables used in one or possibly both of your ON clauses are not of the same time, one is character and the other is numeric.

So you will need to make them of the same type for the comparison. That could involve a Put(variable,format) to make the numeric version character as in (dummy code)

on a.variable = put(b.variable,8. -L)

OR use input to create a numeric version from the character.

You will have to look at the properties of your variables such as with Proc Contents to determine which is which.

Note: a common issue causing like named variables to be of different types is reliance on Proc Import to read multiple data sources. Each separate import makes "guesses" as to variable type and length and may result in different types.