BookmarkSubscribeRSS Feed
vishal_p
Calcite | Level 5
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
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Cynthia_sas
SAS Super FREQ
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
Cynthia_sas
SAS Super FREQ
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
vishal_p
Calcite | Level 5
Thanks a lot for ur help..
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 829 views
  • 0 likes
  • 4 in conversation