BookmarkSubscribeRSS Feed
yonib
SAS Employee
Hi ,
this is my first time im using proc report....
my problem is this:

my data for example is:
data a;
length zip $ 5 cty $ 8 varr $ 10;
input zip $ cty $ varr $ sales cost;
label zip="Zip Code"
cty="country"
varr="variety"
sales="monthly sales";
datalines;

52423 Scott Merlot 186 35
52423 Scott Chardonnay 156.61 33
52423 Scott Zinfandel 35.5 38
52423 Scott Merlot 55.3 90
52388 Scott Merlot 122.89 80
52388 Scott Chardonnay 78.22 90
52388 Scott Zinfandel 15.4 12
52200 Adams Merlot 385.51 120
52200 Adams Chardonnay 246 167
52200 Adams Zinfandel 151.1 180
52200 Adams Chardonnay 76.24 190
52199 Adams Merlot 233.03 175
52199 Adams Chardonnay 185.22 155
;
run;

im trying to create a new column that divide sales with cost

proc report data=a;
title sample10;
column cty zip varr,(sales cost example);

define cty/group width=9 'country/name';
define zip/group 'îé÷åã';
define varr/across 'variety';
define sales/sum format=6.2 'ñëåí îëéøåú';
define example/computed;

compute example;
example=sales/cost;
endcomp;
run;
then new column is getting in all the rows null.
can someone please explain me how to do it (if its possible with this example)?

Thanks in advance
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
Hi:
If you had a simple column statement with NO across usage variables like this, then the compute block would be simple:
[pre]
column region subsidiary product sales returns calc_col;
... more code ...
compute calc_col;
calc_col = divide(sales.sum,returns.sum);
endcomp;
[/pre]

In the above scenario, there is no ambiguity about how sales and returns are being used to create the new report item, CALC_COL. All you have to do is use the expected compound name (assuming that SALES and RETURNS both have a usage of SUM and CALC_COL has a usage of COMPUTED.

You have 5 columns from the data (SASHELP.SHOES) on the report and 1 computed column. Without any across variables, every report row would only need 1 execution of the compute block for CALC_COL.

However, as soon as you define a variable to have the ACROSS usage, then it is possible that your COMPUTE block would execute more than 1 time on each report row. Therefore, this form of assignment statement
[pre]
calc_col = divide(sales.sum,returns.sum);
[/pre]

will no longer work. That's because you will have a CALC_COL for the first ACROSS group, another CALC_COL for the second ACROSS group, and the third, and the fourth, etc, etc. So, in order to write the correct assignment statement, you need to use absolute column numbers for any of the ACROSS items involved in the compute block. For example:
[pre]
column region subsidiary product,(sales returns calc_col);
[/pre]

Now, let's say you had 3 groups of product values. REGION is conceptually the first column on the report; subsidiary is the second column on the report. SALES for the first PRODUCT value is _C3_ as an absolute name. RETURNS for the first PRODUCT value is _C4_ and CALC_COL for the first product value is _C5_. So using these absolute names, the assignment statement would be:
[pre]
... more code ...
compute calc_col;
_c5_ = divide(_c3_,_c4_);
endcomp;
[/pre]

If your PRODUCT had 3 unique values, then your complete COMPUTE block would be:
[pre]
... more code ...
compute calc_col;
_c5_ = divide(_c3_ , _c4_);
_c8_ = divide(_c6_ , _c7_);
_c11_ = divide(_c9_ , _c10_);
endcomp;
[/pre]

Proc REPORT goes through a setup phase when it starts and one of the things it does is assign absolute column numbers to across items during the setup phase. You can detect the absolute column numbers it's going to assign by using OUT= with PROC REPORT to create an output dataset (which for across items, will contain the absolute column names that PROC REPORT assigned):
[pre]

ods html file='c:\temp\shoes_abs.html' style=sasweb;
proc report data=sashelp.shoes nowd out=work.abscol;
title 'ACROSS Example';
where region in ("Asia", "Canada") and
product in ("Sandal", "Boot", "Slipper");

column region subsidiary
product,(sales returns calc_col);

define region / group 'Region';
define subsidiary / group 'Subsidiary';
define product / across 'Product';
define sales / sum format=comma12.2 'Sales';
define returns / sum format=comma12.2 'Returns';
define calc_col / computed format=8.3 ;
compute calc_col;
calc_col = .;
endcomp;
run;

proc print data=work.abscol;
run;
ods html close;
[/pre]

A close examination of WORK.ABSCOL will reveal how the column names work. since I have 3 unique PRODUCT values and 3 items nested under each unique PRODUCT value, it is easy to figure out what the column names will be if I had 4 or 5 unique PRODUCT values. (In fact, if you're comfortable with SAS Macro programming, you can write a macro program to generate the compute block assignment statements for you.) Note that in this output. I set the value of CALC_COL to missing on purpose, because the focus of the above program is to reveal what column names need to be used in the COMPUTE block.

A final version of the above program is shown below.

cynthia

[pre]
ods html file='c:\temp\shoes_acr.html' style=sasweb;
proc report data=sashelp.shoes nowd;
title 'ACROSS Example';
where region in ("Asia", "Canada") and
product in ("Sandal", "Boot", "Slipper");

column region subsidiary
product,(sales returns calc_col);

define region / group 'Region';
define subsidiary / group 'Subsidiary';
define product / across 'Product';
define sales / sum format=comma12.2 'Sales';
define returns / sum format=comma12.2 'Returns';
define calc_col / computed format=8.3 ;
rbreak after / summarize;

compute after region;
line ' ';
endcomp;
compute calc_col;
_c5_ = divide(_c3_ , _c4_);
_c8_ = divide(_c6_ , _c7_);
_c11_ = divide(_c9_ , _c10_);
endcomp;
run;
ods html close;
[/pre]
yonib
SAS Employee
Hi ,cynthia

Thanks alot for your help ,
Your answer was very informative ,
Thaks for your time,
yoni

Merry Christmas
Eva
Quartz | Level 8 Eva
Quartz | Level 8
Hi there,

thanx for this post - it is exactly what I'm dealing with at the moment. But there is one problem: if you want to be flexible and don't always know how many columns your across statement will create (beacause this depends on the data) then the calculation with _Cx_ works only if you determine the number of columns first - that's how I solved it and so I don't depend on the exact number of across values anymore.

By the way I tried something else first, but that only works if you don't want to have a summary line: I calculated the division in a data step and then did the proc report. Works fine only if you use rbreak / summarize the division is also summarized and has therefore the wrong value....

Best regards,
Eva
Cynthia_sas
SAS Super FREQ
Hi:
This is exactly a situation where using SAS Macro programming statements can help you with PROC REPORT. You can write a macro program, similar to the one shown on pages 12-13 here:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

The macro program will generate the correct number of _Cx_ references for you so that when the program goes to the compiler, you will have the correct number, no matter how many are in the data.

cynthia

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, 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
  • 4 replies
  • 831 views
  • 0 likes
  • 3 in conversation