topic proc report making use of a by variable in SAS Procedures

proc report making use of a by variable

deleted_user — Wed, 30 Sep 2009 18:35:51 GMT

I use the following column statement in proc report

column varname
(%do zz=1 %to &mcount; &&mname&zz %end;);;

&&mname&zz contains the text for each month and year in the dataset

I now want to break up the report by year so I've added a by year to the proc report.

How can I test that the column name contains the correct year? When I add the by statement I get lots of blank columns. For example, march2009 is blank when the by value is 2008.

Re: proc report making use of a by variable

sbb — Wed, 30 Sep 2009 20:13:05 GMT

You can use the WHERE statement to control you rinput file selection with PROC REPORT.

You will be better off sharing your SAS code, because it is unclear what SAS CHARACTER or NUMERIC variable(s) you have that are being used.

For your own convenience, consider having a SAS DATE (NUMERIC type) variable in your input file (maybe you already have this?) and use it in your WHERE statement, such as:

* select input observations only for current year;
WHERE YEAR() = YEAR(TODAY());

The alternative if you only have a text-format month/year is to attempt to parse out the year-portion and use it in a WHERE statement.

Some suggested reading on SAS DATE variables pasted below.

Scott Barry
SBBWorks, Inc.

Step-by-Step Programming with Base SAS(R) Software
Working with Dates in the SAS System
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001304321.htm

Re: proc report making use of a by variable

ChrisNZ — Thu, 01 Oct 2009 22:08:05 GMT

Have you tried reorganising your data to use
[pre]define month /across[/pre]
so that the values of
[pre]column &&mname&zz [/pre]
now come from one variable ?

There is no girl aged 16, and the column 16 does not appear for girls in:
[pre]
proc sort data=SASHELP.CLASS out=CLASS;
by SEX;
proc report data=CLASS nowd;
define AGE /across;
define WEIGHT /mean;
column AGE,WEIGHT;
by SEX;
run;
quit;
[/pre]
If you force the column name in the column statement, then that column will be shown.

Re: proc report making use of a by variable

deleted_user — Mon, 05 Oct 2009 17:59:39 GMT

To get it to work I had to create a macro variable

call symput('mmm' || left(cnt), '_C'||compress(monthnum+1)||'_');

where monthnum is the number 1 through 12 of each month. To this I had to add one so in the compute I could refer to the column number