topic Re: Dynamic columns in Proc Report in SAS Procedures

Dynamic columns in Proc Report

tanya_henderson — Fri, 29 Jun 2018 13:00:56 GMT

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;

Re: Dynamic columns in Proc Report

RW9 — Fri, 29 Jun 2018 13:13:33 GMT

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.

Re: Dynamic columns in Proc Report

Astounding — Fri, 29 Jun 2018 13:20:04 GMT

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: ;