Help using Base SAS procedures

Proc report Out Column names

Reply
Super Contributor
Posts: 483

Proc report Out Column names

I have a proc report statement like

proc report data=CombinedpctTableSummary  out=CombinedpctTableSummaryOut;

     column displayColumn academic_period_desc, displayNum;

     define displayNum / analysis '';

     define displayColumn / group '' order = internal;

     define academic_period_desc / across '';

run;

The output includes columns like _C2_ _C3_.  I need a way of setting these to values that relate to the academic period desc.  With a  domain of:

("2011-2012","2012-2013","2013-2014","2014-2015","2015-2016","2016-2017")

or a way to know that _C2_ relates to 2011-2012.

Super User
Posts: 10,517

Re: Proc report Out Column names

You won't have much control over the names created by Proc Report for the variables. However PROC Datasets is designed to modify such things as names or labels of variables in an existing data set.

 

Personally I wouldn't attempt to use a name that look like a date as coding with them would require using name literals such as "2011-2012"n everwhere. But a LABEL should work just fine:

 

Proc datasets libray=work;

   modify CombinedpctTableSummaryOut;

      label _c2_ = "2011-2012"

               _c3_ = "2012-2013"

      ;

run;

quit; /* datasets support run-group so needs quit to end the procedure*/

 

Or Rename if you feel it necessary

Proc datasets libray=work;

   modify CombinedpctTableSummaryOut;

      rename _c2_ = C2011

                   _c3_ = C2012

      ;

run;

quit;

 

or similar. If you Rename AND Label pay attention to order, you could do both in the same Modify but which variable name to use may depend on order of statements.

Super Contributor
Posts: 483

Re: Proc report Out Column names

The problem is by the time that I use proc dataset the columns in the outstatement of proc report are already determined.  If the first year is missing the _C2_ value does not relate to the first year.

Super Contributor
Posts: 483

Re: Proc report Out Column names

This type of stuff works but it takes so long to code.

 

data EnrollmentBlock; set Enrollment;
if Academic_year = '2011-2012' then _2011_12 = headcount;
if Academic_year = '2012-2013' then _2012_13 = headcount;
if Academic_year = '2013-2014' then _2013_14 = headcount;
if Academic_year = '2014-2015' then _2014_15 = headcount;
if Academic_year = '2015-2016' then _2015_16 = headcount;
if Academic_year = '2016-2017' then _2016_17 = headcount;
run;

/******Enrollment, Persistence and Success (Academic Year) Enrollment Block)******/
proc report data=EnrollmentBlock (rename=(level=displayColumn )) out=EnrollmentOut;
column displayColumn _2011_12 _2012_13 _2013_14 _2014_15 _2015_16 _2016_17;
define displayColumn / group '' order = internal;
define _2011_12 / analysis sum '2011-12';
define _2012_13 / analysis sum '2012-13';
define _2013_14 / analysis sum '2013-14';
define _2014_15 / analysis sum '2014-15';
define _2015_16 / analysis sum '2015-16';
define _2016_17 / analysis sum '2016-17';
rbreak after / summarize dol dul ;
where term = 'Fall';
run;

Super User
Posts: 10,517

Re: Proc report Out Column names

Maybe doing too much work.

I don't have data but I might start with something like this:

data enrollmentblock;
   set enrollmentblock;
   year = input(substr(Academic_year,1,4),f4.);
run;
proc format  library=work;
   format AcademYr
2011 = '2011-2012'
2012 = '2012-2013'
2013 = '2013-2014'
2014 = '2014-2015'
2015 = '2015-2016'
2016 = '2016-2017'
;
run;
proc report data=EnrollmentBlock (rename=(level=displayColumn )) out=EnrollmentOut;
column year,headcount;
define year / across format=Academyr.;
define headcount /analysis sum;
run;


Any time you find yourself doing a largish number of single value assignments then you may be better off with a single group/across variable. Note that it is easy to make a single format for Academic year that would work for a very long time using a data step, do loop and cntlin option on proc format so you wouldn't have to re-code it for a long time. I think with 10 to 12 lines of code I could make that data set work from 1900 to 19000.

 

 

Note that the above code, if the format has sufficient values of year set, doesn't care about the content of the data too much for making labels.

 

And if you have date values involved you might be able to get things like Spring 2011-2012. I suspect that a prior step actually involved creating that '2011-2012' value and might not be needed with this approach. The output structure of the report data set may change a bit though.

Super Contributor
Posts: 483

Re: Proc report Out Column names

In this case the columns of the output table are still named C2 - C7.  Rather than by year.

Super Contributor
Posts: 483

Re: Proc report Out Column names

I ran into a complication with my method.  I have an out statement that produces a table with 0 rows, in some permutations.

Because the out statement creates 0 rows a rename block breaks.  I need the rename block to set the length of display column to 100.

 

proc report data=ug_student_course_Consumption out=ug_student_course_ConsumptionOut;

     column displayColumn _2011_12 _2012_13 _2013_14 _2014_15 _2015_16 _2016_17;

     define displayColumn / group '' order = internal;

     define _2011_12 / analysis sum '2011-12';

     define _2012_13 / analysis sum '2012-13';

     define _2013_14 / analysis sum '2013-14';

     define _2014_15 / analysis sum '2014-15';

     define _2015_16 / analysis sum '2015-16';

     define _2016_17 / analysis sum '2016-17';

     rbreak after / summarize dol dul ;

run;

Data ug_student_course_ConsumpOut2; length DisplayColumn $100.; set ug_student_course_ConsumptionOut (rename=(DisplayColumn = DisplayColumn2));

     if DisplayColumn2 = '' then DisplayColumn = 'UG SCH by Majors (Overall-Consumption)';

     else DisplayColumn = DisplayColumn2;

Run;

 

ERROR: Variable DisplayColumn is not on file WORK.UG_STUDENT_COURSE_CONSUMPTIONOUT.

ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.UG_STUDENT_COURSE_CONSUMPTIONOUT.

Super Contributor
Posts: 483

Re: Proc report Out Column names

I guess one work around is to use an if statement to check if a table has rows before using the rename statement.

Ask a Question
Discussion stats
  • 7 replies
  • 172 views
  • 0 likes
  • 2 in conversation