Dynamic columns in Proc Report

[tanya_henderson]

I have a performance report where the columns are generated by quarter. I want to know if there is a way to add in all the columns and leverage some kind of "If exists" option to prevent errors when running the report.

Here is a code snippet of what I mean.  I want to add in all of the quarters so they appear in the correct order. If they don't exist yet, the report will error out. How do I handle this dynamic column issue?

proc report data=site_perf_time missing split='?' spanrows;

columns  consented enrolled metric_name Current Y2017Q4 Y2018Q1 Y2018Q2 Y2018Q3 Y2018Q4 ; 
    
     define consented/ group "Consented"; 
     define enrolled/ group "Enrolled"; 
     define metric_name/ order "Metric"; 
run;

[RW9 · Accepted solution]

Several ways.  First one is to have the variables in the right order in your dataset, then you don't need to do anything other than:

columns _all_;

And they will come out in the dataset order.

The second is to create a list of variables from metadata:

proc sql noprint;
  select distinct name 
  into :vlist separated by " "
  from dictionary.columns
  where libname="WORK" 
    and memname="SITE_PERF_TIME" 
    and char(name,1)="Y";
quit;

proc report...
  columns consented ... current &vlist.;
...
run;

Alternatively you could have the year and quarter as observations, then use an across statement to transpose them up.

[Astounding]

Quick and easy tricks to try ...

This might work, or it might not give you enough control over the order:

columns  consented enrolled metric_name Current Y2017Q4 Y2018Q:; 

This might work, or might give you an error (one way to find out):

columns  consented enrolled metric_name Current Y2017Q4 Y2018Q1: Y2018Q2: Y2018Q3: Y2018Q4: ;