topic Re: OUTPUT PROCEDURE in SAS Procedures

OUTPUT PROCEDURE

deleted_user — Thu, 13 Mar 2008 17:02:28 GMT

Hi everyone, I’m very new to SAS but have some analysis that is helping me improve my knowledge. I’m trying to produce a table that generates a value(s) that is derived from a dataset. The following code generates the dataset

Data Example;
Input LastName $3. Region $1. Units CostX CostY CostZ;

Datalines;
XBL0 219 157 66 47
PYG0 674 665 54 23
MPA0 740 269 47 98
RJE1 177 136 74 36
MTN1 518 671 43 93
RJE1 625 762 73 93
NWG2 197 393 98 16
RTS2 341 648 26 18
HCP2 834 116 36 49
Run;

So with the dataset example, I’m trying to get a table that gives me an output table that has a sorting by region and gives result for CostX/Units, CostY/Units and CostY/Units.

I expect it look something like this

Region CostX/Units CostY/Units CostY/Units
1
2
3

Thanks,
Bee

Re: OUTPUT PROCEDURE

Cynthia_sas — Thu, 13 Mar 2008 18:04:02 GMT

Hi,
I think you probably want PROC REPORT. Or, you could do it all in a DATA step.
See the following code.
cynthia
[pre]

ods listing;
ods html file='c:\temp\outtable.html' style=sasweb;

title 'Data Step Output';
Data Example;
infile datalines;
Input LastName $3. Region $1. Units CostX CostY CostZ;
file print ods=(variables=(Region CostX_U CostY_U CostZ_U));
costx_u = costx / units;
costy_u = costy / units;
costz_u = costz / units;
put _ods_;
return;
Datalines;
XBL0 219 157 66 47
PYG0 674 665 54 23
MPA0 740 269 47 98
RJE1 177 136 74 36
MTN1 518 671 43 93
RJE1 625 762 73 93
NWG2 197 393 98 16
RTS2 341 648 26 18
HCP2 834 116 36 49
;
Run;

title 'Proc Report Output';
proc report data=example nowd;
column region costx costy costz units costx_u costy_u costz_u;
define region / display;
define costx / noprint;
define costy / noprint;
define costz / noprint;
define units / noprint;
define costx_u / computed "CostX / Units";
define costy_u / computed "CostY / Units";
define costz_u / computed "CostZ / Units";
compute costx_u;
costx_u = costx.sum / units.sum;
endcomp;
compute costy_u;
costy_u = costy.sum / units.sum;
endcomp;
compute costz_u;
costz_u = costz.sum / units.sum;
endcomp;
run;

ods _all_ close;
ods listing;
[/pre]

Re: OUTPUT PROCEDURE

deleted_user — Thu, 13 Mar 2008 18:15:55 GMT

Thanks Cynthia, It helped but I was hoping to have a summary by the region where all the observations that equal to region 0, 1 and 2 are summed and the Cost_X, Cost_Y and Cost_Z are obtained.
Bee

Re: OUTPUT PROCEDURE

Cynthia_sas — Thu, 13 Mar 2008 19:29:53 GMT

Hi:
I don't know what you mean by
"the Cost_X, Cost_Y and Cost_Z are obtained." ... but if you want to see theses 3 numbers on the report, then either add them to the variables= option or remove the NOPRINT from them in the PROC REPORT step (as shown below).

But, summary lines are easy to produce with PROC REPORT.

cynthia

[pre]

ods listing;
ods html file='c:\temp\outtable.html' style=sasweb;

title 'Data Step Output';
Data Example;
length region $11;
infile datalines;
Input LastName $3. Region $1. Units CostX CostY CostZ;
file print ods=(variables=(Region CostX_U CostY_U CostZ_U));
costx_u = costx / units;
costy_u = costy / units;
costz_u = costz / units;
put _ods_;
return;
Datalines;
XBL0 219 157 66 47
PYG0 674 665 54 23
MPA0 740 269 47 98
RJE1 177 136 74 36
MTN1 518 671 43 93
RJE1 625 762 73 93
NWG2 197 393 98 16
RTS2 341 648 26 18
HCP2 834 116 36 49
;
Run;

title 'Proc Report Output';
proc report data=example nowd
style(summary)=Header;
column region costx costy costz units newx_u newy_u newz_u;
define region / order;
define costx / 'CostX';
define costy / 'CostY;
define costz / 'CostZ';
define units / 'Units';
define newx_u / computed "CostX / Units";
define newy_u / computed "CostY / Units";
define newz_u / computed "CostZ / Units";
break after region / summarize;
rbreak after /summarize;
compute after region;
region = 'Total';
endcomp;
compute after;
region = 'All Regions';
endcomp;
compute newx_u;
newx_u = costx.sum / units.sum;
endcomp;
compute newy_u;
newy_u = costy.sum / units.sum;
endcomp;
compute newz_u;
newz_u = costz.sum / units.sum;
endcomp;
run;

ods _all_ close;
ods listing;

[/pre]

Re: OUTPUT PROCEDURE

deleted_user — Fri, 14 Mar 2008 14:23:01 GMT

Sorry for not being clear on what I want. I'll try to explain a little more. The dataset 'example' is just a subset of a much larger dataset that has about 200 observations. The dataset 'example' has the following variables LastName Region Units CostX CostY CostZ . I want a table that first sums all the Units, CostX, CostY and CostZ that fall under Region (0,1 and 2). Then the operations for CostX/Units, CostY/Units and CostZ/Units by Regions. So that the final output will be table that has Regions just the 3(0,1,2) and not all the 9 observations and not 200 in may larger dataset. The codes you gave still gives me all 9 observations.
Thanks

Re: OUTPUT PROCEDURE

deleted_user — Fri, 14 Mar 2008 14:48:05 GMT

I also think there is some error with code. I corrected a missing ' ,but there is still some error. Please could run the program to confirm.
Thanks

Re: OUTPUT PROCEDURE

Cynthia_sas — Fri, 14 Mar 2008 17:44:42 GMT

Hi:
Thanks for pointing out that missing quote. I fixed it, but then neglected to save that version of the program to where I was cutting and pasting from.

I am still not clear on what you mean, exactly. PROC REPORT will do both DETAIL reports and SUMMARY reports. A detail report has one report row for every observation in the data set or subset. Any report with an ORDER usage (such as I have for REGION) will be a DETAIL report -- unless the data were pre-summarized. On the other hand, the second PROC REPORT, below has a usage of GROUP for REGION and I have 1 report row that represents the SUMMARY of all the rows for REGION=0, REGION=1, etc.

Since the code is getting quite lengthy and since you have very specific formatting needs and data, you might consider contacting Tech Support for help with your specific report and the right PROC REPORT code to produce what you want.

cynthia
[pre]
ods listing;
ods html file='c:\temp\outtable.html' style=sasweb;

title 'Data Step Output';
Data Example;
length region $11;
infile datalines;
Input LastName $3. Region $1. Units CostX CostY CostZ;
file print ods=(variables=(Region CostX_U CostY_U CostZ_U));
costx_u = costx / units;
costy_u = costy / units;
costz_u = costz / units;
put _ods_;
return;
Datalines;
XBL0 219 157 66 47
PYG0 674 665 54 23
MPA0 740 269 47 98
RJE1 177 136 74 36
MTN1 518 671 43 93
RJE1 625 762 73 93
NWG2 197 393 98 16
RTS2 341 648 26 18
HCP2 834 116 36 49
;
Run;

title 'Proc Report Detail Report';
proc report data=example nowd
style(summary)=Header;
column region costx costy costz units newx_u newy_u newz_u;
define region / order;
define costx / 'CostX';
define costy / 'CostY';
define costz / 'CostZ';
define units / 'Units';
define newx_u / computed "CostX / Units";
define newy_u / computed "CostY / Units";
define newz_u / computed "CostZ / Units";
break after region / summarize;
rbreak after /summarize;
compute after;
region = 'All Regions';
endcomp;
compute newx_u;
newx_u = costx.sum / units.sum;
endcomp;
compute newy_u;
newy_u = costy.sum / units.sum;
endcomp;
compute newz_u;
newz_u = costz.sum / units.sum;
endcomp;
run;

title 'Proc Report Output Summary Report';
proc report data=example nowd
style(summary)=Header;
column region costx costy costz units newx_u newy_u newz_u;
define region / group;
define costx / 'CostX';
define costy / 'CostY';
define costz / 'CostZ';
define units / 'Units';
define newx_u / computed "CostX / Units";
define newy_u / computed "CostY / Units";
define newz_u / computed "CostZ / Units";

rbreak after /summarize;
compute after region;
region = 'Total';
endcomp;
compute after;
region = 'All Regions';
endcomp;
compute newx_u;
newx_u = costx.sum / units.sum;
endcomp;
compute newy_u;
newy_u = costy.sum / units.sum;
endcomp;
compute newz_u;
newz_u = costz.sum / units.sum;
endcomp;
run;
ods _all_ close;
ods listing;

[/pre]

The code runs for me and I get 2 outputs in the LISTING window. as shown below:
[pre]
Detail Report

Proc Report Detail Report

CostX CostY CostZ
region CostX CostY CostZ Units Units Units Units
0 157 66 47 219 0.716895 0.3013699 0.2146119
665 54 23 674 0.9866469 0.0801187 0.0341246
269 47 98 740 0.3635135 0.0635135 0.1324324
Total 1091 167 168 1633 0.6680955 0.1022658 0.1028781
1 136 74 36 177 0.7683616 0.4180791 0.2033898
671 43 93 518 1.2953668 0.0830116 0.1795367
762 73 93 625 1.2192 0.1168 0.1488
Total 1569 190 222 1320 1.1886364 0.1439394 0.1681818
2 393 98 16 197 1.9949239 0.4974619 0.0812183
648 26 18 341 1.9002933 0.0762463 0.0527859
116 36 49 834 0.1390887 0.0431655 0.058753
Total 1157 160 83 1372 0.8432945 0.1166181 0.0604956
All Regions 3817 517 473 4325 0.8825434 0.1195376 0.1093642

*******************************************************************
Order Report
Proc Report Output Summary Report
CostX CostY CostZ
region CostX CostY CostZ Units Units Units Units
0 1091 167 168 1633 0.6680955 0.1022658 0.1028781
1 1569 190 222 1320 1.1886364 0.1439394 0.1681818
2 1157 160 83 1372 0.8432945 0.1166181 0.0604956
All Regions 3817 517 473 4325 0.8825434 0.1195376 0.1093642

[/pre]

Re: OUTPUT PROCEDURE

deleted_user — Fri, 14 Mar 2008 19:50:54 GMT

THANKS CYNTHIA