Select variable only if exists SAS SQL

Question

PROC SQL;
    CREATE TABLE SET3 AS 
    SELECT 
        a.VAR1 , b.VAR1 AS P_VAR1 , 
        a.VAR2          
    FROM SET1 AS a FULL JOIN SET2 AS B
    ON  a.VAR1 = b.VAR1 
 ;
QUIT;

The code above creates a table called SET3 and includes VAR1 from 2 datasets, along with VAR2 where a.VAR1=b.VAR1.

I want this code to run to include VAR2 if it is available. It is sometimes not and so the code crashes.

I could do this by writing the code twice with some macros but was wondering if there was a quick, SQL syntax which could be used.

Many thanks!

Answer 1

One option is to create a macro variable that will either have VAR1 or VAR1,VAR2 depending on whether it exists. dictionary.columns is the metadata table you can get this from, which is available in proc sql.

The order of the final data will be slightly different (a.VAR1, a.VAR2, b.VAR1), but hopefully that won't be an issue.

proc sql noprint;
select cats('a.',name) into :varlist separated by ','
from dictionary.columns
where libname='WORK' and memname='SET1' and name in ('VAR1','VAR2');
quit;

%put &=varlist.;

PROC SQL;
    CREATE TABLE SET3 AS 
    SELECT 
        &varlist. , b.VAR1 AS P_VAR1
    FROM SET1 AS a FULL JOIN SET2 AS B
    ON  a.VAR1 = b.VAR1 
 ;
QUIT;