topic Re: Creating PROC REPORT using BY statement in SAS Programming

Creating PROC REPORT using BY statement

Sami1234 — Fri, 25 May 2018 09:00:52 GMT

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?

Re: Creating PROC REPORT using BY statement

RW9 — Fri, 25 May 2018 09:07:39 GMT

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.

Re: Creating PROC REPORT using BY statement

Sami1234 — Fri, 25 May 2018 09:17:56 GMT

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

Re: Creating PROC REPORT using BY statement

Sami1234 — Fri, 25 May 2018 13:03:48 GMT

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,

Re: Creating PROC REPORT using BY statement

RW9 — Fri, 25 May 2018 13:16:42 GMT

"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.

Re: Creating PROC REPORT using BY statement

Sami1234 — Fri, 25 May 2018 14:03:37 GMT

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?

Re: Creating PROC REPORT using BY statement

Sami1234 — Fri, 25 May 2018 14:06:37 GMT

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

Re: Creating PROC REPORT using BY statement

RW9 — Fri, 25 May 2018 14:12:19 GMT

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;

Re: Creating PROC REPORT using BY statement

ballardw — Fri, 25 May 2018 14:36:59 GMT

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

Re: Creating PROC REPORT using BY statement

Sami1234 — Fri, 25 May 2018 14:49:22 GMT

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?