Forcing proc export to create blank column for non-existing variable

Question

Background

I'm running the below macro to export a number of data sets in the loop. The unfortunate situation is that some of those data sets have extra variables. My intention is to export all the files in the same format where non-existing columns are provided as blanks.

Data

This can be illustrated with the following data sets.

dataA

varA varB
1    3
2    3
3    3

dataB

varA varB
9    4
9    4
9    4

dataC

varA varB varC
2    5    6
2    5    6
2    5    6

I would like my CSV files to look as follow:

dataA.CSV

varA varB varC
1    3    .
2    3    .
3    3    .

dataB.CSV

varA varB varC
9    4    .
9    4    .
9    4    .

dataC.CSV

varA varB varC
2    5    6
2    5    6
2    5    6

Macro

%macro export_data(dsnms);
    * Get observations count;
    PROC SQL;
        SELECT COUNT(*) INTO :obscount
            FROM &dsnms;
    QUIT;

    * Export all available files in the loop;
    %Local D;

    %DO D = 1 %TO &obscount;

        * Print progress message;
        sysecho "Progressing through &D of &obscount";

        * Get table name;
        PROC SQL;
            SELECT COMPRESS(MEMNAME) INTO: Table
                FROM &dsnms
                    WHERE rownum=&D;
        QUIT;

        * Extra spaces in file name are removed via cmpres call;
        PROC EXPORT DBMS=CSV DATA=SASLIBWITHSTUFF.&Table
            OUTFILE="/mystuff/%cmpres(&Table).csv";
        RUN;

        * Inform about succesful export;
        sysecho "Created &Table..csv export file.";
    %END;
%mend;

Summary

In other words, I would like modify the behaviour of proc export to create NonExistingVar if such is passed in the Kepp =. Naturally, this will fail as sashelp.class does not have NonExistingVar but this is the behaviour I want to emulate.

proc export data=sashelp.class (keep =  name
                                        sex
                                        NonExistingVar)
   outfile="/stuff/proc_test.csv"
   dbms=csv
   replace;
run;

Answer 1

Provided you have a macro variable somewhere that contains a list of the columns you want to export, you can construct a view containing them, including the ones that aren't present in the underlying dataset, and export the view instead. E.g.

%let keepcols = sex weight newcol;

data t_view /view = t_view;
  if 0 then set sashelp.class; 
  if _n_ = 1 then call missing(of &keepcols);
  set sashelp.class;
run;

The if 0 then set xyz is a nice little trick that allows you to preserve column order, lengths and types without ever reading in any rows from the first set statement. It avoids clashing types when subsequently setting variables to missing values - we have to initialise them to something otherwise they aren't output. The second set statement will then overwrite missing values for variables that are actually present.

This could be made a bit more efficient if you queried the the metadata tables to determine which of your kept columns are present, so you would only load those ones, but this should be reasonable as is for most cases.

A lazier option is to temporarily disable keep-related errors and warnings, e.g.:

%let keepcols = sex weight newcol;

%let dkricond = %sysfunc(getoption(dkricond)); /*Save for later*/
option dkricond = nowarn;

data t_view /view = t_view;
  if 0 then set sashelp.class(keep = &keepcols); /*Normally this would trigger an error or warning*/
  retain &keepcols;
  set sashelp.class(keep = &keepcols); 
run;

option dkricond = &dkricond; /*Restore original setting*/