Get data filtered by dynamic column list in SAS stored process

Question

My goal is to create a SAS stored process is to return data for a single dataset and to filter the columns in that dataset based on a multi-value input parameter passed into the stored process.

Is there a simple way to do this?

Is there a way to do this at all?

Here's what I have so far. I'm using a macro to dynamically generate the KEEP statement to define which columns to return. I've defined macro variables at the top to mimic what gets passed into the stored process when called through SAS BI Web Services, so unfortunately those have to remain as they are. That's why I've tried to use the VVALUEX method to turn the column name strings into variable names.

Note - I'm new to SAS

libname fetchlib meta library="lib01" metaserver="123.12.123.123"
password="password" port=1234
repname="myRepo" user="myUserName";

/* This data represents input parameters to stored process and 
 * is removed in the actual stored process*/
%let inccol0=3;
%let inccol='STREET';
%let inccol1='STREET';
%let inccol2='ADDRESS';
%let inccol3='POSTAL';
%let inccol_count=3;

%macro keepInputColumns;
    %if &INCCOL_COUNT = 1 %then
        &inccol;
    %else
        %do k=1 %to (&INCCOL_COUNT);
             var&k = VVALUEX(&&inccol&k);
        %end;
        KEEP
        %do k=1 %to (&INCCOL_COUNT);
             var&k
        %end;
        ;
%mend;

data test1;
    SET fetchlib.Table1;
    %keepInputColumns;
run;

/*I switch this output to _WEBOUT in the actual stored process*/
proc json out='C:\Logs\Log1.txt';
    options firstobs=1 obs=10;
   export test1 /nosastags;
run;

There are some problems with this. The ouput uses var1, var2 and var3 as the column names and not the actual column names. It also doesn't filter by any columns when I change the output to _webout and run it using BI Web Services.

Answer 1

It turns out that the simplest way to implement this was to change the way that the columns (aka SAS variables) were passed into the stored process. Although Joe's answer was helpful, I ended up solving the problem by passing in the columns to the keep statement as a space-separated column list, which greatly simplified the SAS code because I didn't have to deal with a dynamic list of columns.

libname fetchlib meta library="lib01" metaserver="123.12.123.123"
password="password" port=1234
repname="myRepo" user="myUserName";"&repository" user="&user";

proc json out=_webout;
   export fetchlib.&tablename(keep=&columns) /nosastags;
run;

Where &columns gets set to something like this:

Column1 Column2 Column3