Help using Base SAS procedures

Dynamic columns in Proc Report

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Dynamic columns in Proc Report

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;

 


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 9,599

Re: Dynamic columns in Proc Report

Posted in reply to tanya_henderson

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.

 

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Super User
Posts: 9,599

Re: Dynamic columns in Proc Report

Posted in reply to tanya_henderson

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.

 

Super User
Posts: 6,751

Re: Dynamic columns in Proc Report

Posted in reply to tanya_henderson

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 140 views
  • 2 likes
  • 3 in conversation