topic Re: proc report in SAS Procedures

proc report

vishal_p — Tue, 28 Sep 2010 05:36:54 GMT

Hello,

I need to produce the below report..Can this be done using proc report...

Type---Transaction----Month
-----------------------------Jan-Feb-mar-apr-may=till Dec
x---------a
----------b
y---------c
----------d
z---------e
----------f

Here Type Transaction and Month are headers...

All the remaining are Column names.

X, Y and Z should be grouped under Type header.
a,b,c,d,e,f should be grouped under transaction header.

Can the report be put in such complex group structure..

Please help

Message was edited by: vishal_p

Message was edited by: vishal_p

Message was edited by: vishal_p

Message was edited by: vishal_p Message was edited by: vishal_p

Re: proc report

sbb — Tue, 28 Sep 2010 10:51:04 GMT

Yes - look at using the ACROSS option with a SAS DATE variable and an output FORMAT such as WORDDATE3. possibly.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

proc report example across site:sas.com

proc report documentation across site:sas.com

format worddate site:sas.com

Re: proc report

Cynthia_sas — Tue, 28 Sep 2010 17:16:20 GMT

Hi:
I agree with Scott that generally, you would do this type of report with ACROSS usage for your MONTH variable. However something about your statement is confusing. You said:

Here Type Transaction and Month are headers...All the remaining are Column names.

Are you really saying that X, Y and Z are column NAMES -- or are they VALUES for TYPE?????? Are JAN, FEB, MAR, etc column NAMES or are they VALUES for MONTH -- so that every unique value for MONTH is a separate column on the report????

Just checking.

cynthia

Re: proc report

Ksharp — Wed, 29 Sep 2010 01:18:56 GMT

En.proc report could do it.
But your data structure is obscure.
So giving some example about your original data would be very help to generate the result you need.

Ksharp

Re: proc report

Cynthia_sas — Wed, 29 Sep 2010 02:48:59 GMT

Hi:
Here's a concrete example. SASHELP.PRDSALE has 2 years of data for 1993 and 1994. There is a variable called MONTH that is a SAS date variable formatted with the MONNAME3. format. Other variables or columns are COUNTRY (with 3 possible values -- U.S.A., CANADA and GERMANY), REGION (with 2 possible values (EAST and WEST) and DIVISION (with 2 possible values CONSUMER and EDUCATION).

Every observation in the file represents information on ACTUAL and PREDICTED sales for one COUNTRY/REGION/DIVISION/YEAR/MONTH, as shown below (only 10 obs are shown and MONTH is formatted with DATE9 for this list of observations):
[pre]
COUNTRY REGION DIVISION YEAR MONTH ACTUAL PREDICT

CANADA EAST EDUCATION 1994 01JAN1994 $107.00 $190.00
CANADA EAST EDUCATION 1994 01JAN1994 $267.00 $410.00
CANADA EAST EDUCATION 1994 01JAN1994 $355.00 $497.00
CANADA EAST EDUCATION 1994 01JAN1994 $877.00 $795.00
CANADA EAST EDUCATION 1994 01JAN1994 $942.00 $672.00
CANADA EAST CONSUMER 1994 01JAN1994 $944.00 $965.00
CANADA EAST CONSUMER 1994 01JAN1994 $312.00 $919.00
CANADA EAST CONSUMER 1994 01JAN1994 $585.00 $12.00
CANADA EAST CONSUMER 1994 01JAN1994 $641.00 $425.00
CANADA EAST CONSUMER 1994 01JAN1994 $689.00 $523.00
[/pre]

If you want to create a report with the months for 1994 going across the top of the report -- so there is a unique column for every month, then the following code will accomplish that task -- shows the ACTUAL sales total for every month.

[pre]
** make a subset for 1994 only;
proc sort data=sashelp.prdsale out=prdsale;
by year month;
where year = 1994;
run;

** create a report with ACROSS usage for MONTH variable;
ods listing close;
ods html file='c:\temp\show_across.html' style=sasweb;
proc report data=prdsale nowd;
title 'Actual Sales by Month in 1994';
column country region division actual,month;
define country / group;
define region / group;
define division / group;
define month / across order=internal f=monname3.;
define actual / sum ' ';
rbreak after / summarize;
run;
ods html close;
[/pre]

The syntax construction ACTUAL,MONTH tells PROC REPORT to use the value of the ACTUAL variable for each month's numbers. If you wanted to get a count of observations for each month, then you would only need to remove the DEFINE statement for ACTUAL from the PROC REPORT code and change the COLUMN statement to:
[pre]
column country region division month;
[/pre]

Whether you show the values for ACTUAL or get just a COUNT (the N statistic), listing these variables from left to right: COUNTRY REGION DIVISION and giving them all a usage of GROUP tells PROC REPORT that COUNTRY will be the left-most column and unique values for REGION will be nested inside each value of COUNTRY and unique values for DIVISION will be nested inside REGION. A usage of GROUP also "collapses" or summarizes observations. So each report row will represent the summary numbers for CANADA, EAST, CONSUMER and then CANADA, EAST, EDUCATION, etc -- so that even if there were 1000 observations for the unique combination of COUNTRY=CANADA, REGION=EAST, and DIVISION=CONSUMER, those 1000 observations would get summarized down to one report row. (and the same would be true for every unique combination of COUTNRY, REGION and DIVISION).

Perhaps this will give you an idea of how PROC REPORT can create a summary report with every value for a variable being to create a report column.

cynthia

Re: proc report

vishal_p — Fri, 01 Oct 2010 06:31:35 GMT

Thanks a lot for ur help..