Creating PROC REPORT using BY statement

[Sami1234]

Hi,

I would like to create a large demographic table by each country.

Wondering if there is a way to create a table using 'by statement' and changing the first column name as per the country?

at the moment I am running the proc report 20 times (20 countries) and changing the column name manually?

[RW9]

Its generally a good idea to have the title or pre-text as the group, not the column name.  E.g.

proc report data...;
  by country;
  title "Country: #byval1";
...
run;

I don't think byval works in label statement, but you can try it:

proc report data...;
  by country;
  define country / label="#byval1";
...
run;

If not you will need to do it by code generation:

proc sort data=have out=loop nodupkey;
  by country;
run;
data _null_;
  set loop;
  call execute(cats('proc report...; where country="',country,'"; define country / label="',country,'"; run;'));
run;

However I really suggest you change your report spec as changing column names isn't good.

[Sami1234]

Hi, 

Thanks for the reply.

As per output requirement, I've to create one large report with by country title and then the first columns ' label (which has all the demo variables names) will show the country name.

I will try the above solution and let you know then 🙂 thanks

[Sami1234]

Hi,

I tried both methods and they are not working.

The second method is not very clear to me. where do I need to put where statement.

Many thanks,

[RW9]

"they are not working." - this does not tell us anything. 

This line:

  call execute(cats('proc report...; where country="',country,'"; define country / label="',country,'"; run;'));

Creates a proc report step for each unique country as defined in the dataset loop.  The where is part of that.

I have no idea of what you have - no test data in the form of a datastep, nor what you want output, so its hard to provide any guidance. 

[Sami1234]

Hi,

I would like to run a demographic table by country and the variable 'country' should not be in the report, the country is only showing it as one of the variable's label.

Somehow call execute is throwing errors about where statement and missing brackets, wondering any other way to create report?

[Sami1234]

would it be possible to use do until(endoffile) or macro to run till end of file?

[RW9 · Accepted solution]

If you don't want country to show then set it as noprint:

proc report data...;
  by country;
  title "Country: #byval1";
  columns _all_;
  define country / noprint;
...
run;

[ballardw]

Some example data and maybe the code you are attempting that does a single country would be very helpful.

[Sami1234]

Proc report data=test;

column country allgrp trt1 trt2 trt3 trt4 total;

define allgrp / 'country' ;

I would like to see the allgrp label as a country?