topic Re: Left join with a table that is not exisiting in SAS Programming

Left join with a table that is not exisiting

Ronein — Sun, 19 Jul 2020 06:35:33 GMT

Hello

I want to perform left join with a data set that is not existing.

I want to create a dynamic process that IF data set bbb doesn't exist then I will not get an error .

I get an error

37 QUIT;
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE.

ERROR 76-322: Syntax error, statement will be ignored."

data aaa;
input id x;
cards;
1 10
2 20
3 30
;
run;
 
%macro xxx;
PROC SQL;
	create table wanted  as
	select  a.*,b.Y
	from  aaa as a
	%if %sysfunc(exist(bbb)) %then
	left join bbb as b
	on a.id=b.id  
;
QUIT;
%mend xxx;
%xxx;

Re: Left join with a table that is not exisiting

Kurt_Bremser — Sun, 19 Jul 2020 06:52:45 GMT

You are missing a semicolon (one is needed to terminate the %IF, the other for the SQL SELECT).

Since SAS 9.4M6, you do not need a macro definition anymore:

proc sql;
create table wanted  as
  select a.*, b.Y
  from aaa as a
%if %sysfunc(exist(bbb)) %then %do;
  left join bbb as b
  on a.id = b.id  
%end;
;
quit;

Re: Left join with a table that is not exisiting

Ronein — Sun, 19 Jul 2020 07:03:45 GMT

Hello

I still recieve an error (I am using SAS enterprise guide 7.1)

data aaa;
input id x;
cards;
1 10
2 20
3 30
;
run;


proc sql;
create table wanted  as
  select a.*, b.Y
  from aaa as a
%if %sysfunc(exist(bbb)) %then %do;
  left join bbb as b
  on a.id = b.id  
%end;
;
quit;
/*ERROR: The %IF statement is not valid in open code.*/


%macro RRR;
proc sql;
create table wanted  as
  select a.*, b.Y
  from aaa as a
%if %sysfunc(exist(bbb)) %then %do;
  left join bbb as b
  on a.id = b.id  
%end;
;
quit;
%mend RRR;
%RRR;
/*ERROR: Unresolved reference to table/correlation name b.*/

Re: Left join with a table that is not exisiting

Kurt_Bremser — Sun, 19 Jul 2020 07:06:23 GMT

You need to enclose

, b.y

in a similar ⁵IF-%THEN-%DO-%END block.

Re: Left join with a table that is not exisiting

Ronein — Sun, 19 Jul 2020 07:09:50 GMT

Thank you

Still got an error

___
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS,
CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

%macro RRR;
proc sql;
create table wanted  as
  select a.*,%if %sysfunc(exist(bbb)) %then %do; b.Y %end;
  from aaa as a
%if %sysfunc(exist(bbb)) %then %do;
  left join bbb as b
  on a.id = b.id  
%end;
;
quit;
%mend RRR;
%RRR;

Re: Left join with a table that is not exisiting

Kurt_Bremser — Sun, 19 Jul 2020 07:21:40 GMT

Please pay attention to detsils!

You will see that the comma is part of the conditional code.

Re: Left join with a table that is not exisiting

Kurt_Bremser — Tue, 21 Jul 2020 08:44:34 GMT

To be able to use %IF-%THEN %DO-%END in open code, you need to have the current maintenance level of SAS 9.4, which is M6. I mentioned that earlier.