topic proc sql left join if data set is not exisiting in SAS Programming

proc sql left join if data set is not exisiting

Ronein — Mon, 13 Dec 2021 05:50:13 GMT

Hello

I want to perform left join via proc sql and it might happen the the data set is not existing.

In such case I want that the wanted data set will be created without fields from non existing data set (sex,revenue).

I get an error in this code,

may anyone help please?

ERROR: There is no matching %DO statement for the %END. This statement will be ignored.

Data AAA;
input ID X y ;
cards;
1 10 20
2 30 40
3 50 60
;
Run;


%macro RRR;
proc sql;
create table wanted as
select a.*,
        %if %sysfunc(exist(BBB)) %then %do; 
		b.revenue,
        b.Sex
		%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;
/*ERROR: There is no matching %DO statement for the %END. This statement will be ignored.*/

Re: proc sql left join if data set is not exisiting

PeterClemmensen — Mon, 13 Dec 2021 06:29:47 GMT

You just have to get the commas and semicolons right 🙂

Data AAA;
input ID X y ;
cards;
1 10 20
2 30 40
3 50 60
;
Run;


%macro RRR;
proc sql;
create table wanted as
select a.*
        %if %sysfunc(exist(BBB)) %then %do; 
        ,b.revenue
        ,b.Sex
      %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;

options mprint;

%RRR

Re: proc sql left join if data set is not exisiting

Reeza — Mon, 13 Dec 2021 16:16:15 GMT

For simplicity I'd code that differently - check if the data set exists and if it does do the join, if not then add the empty columns alone without a SQL query using base macro logic. Simpler code is easier to maintain.