topic Re: Proc Print Question in SAS Procedures

Proc Print Question

Nooby1Konoby — Mon, 01 Aug 2011 19:07:37 GMT

I'm working with Proc Print and was curious if there is a way to take the SUM totals (in RED) and find the percent of them but do it in the Proc Print step of my batch job? Basically i'm wanting ALLOC / CAP * 100. which would be about 60.2, but i want to do that in the proc print step below, I dont want to have to rewrite my whole mainframe batch job. Any help would be most appreciated.

SUBSYS CAP ALLOC AVAL PERCNTUSED

2107#1 67.94 49.26 18.68 72.5

2107#2 67.94 50.93 17.01 75.0

2107#3 69.39 39.54 29.85 57.0

2107#4 67.24 24.94 42.30 37.1

====== ====== ======

272.51 164.67 107.84

Here is my proc print code that produces the above output.

PROC PRINT DATA = NEW NOOBS;

SUM CAP ALLOC AVAL;

VAR SUBSYS CAP ALLOC AVAL PERCNTUSED;

Proc Print Question

Cynthia_sas — Mon, 01 Aug 2011 19:41:42 GMT

Hi:

You can do something like that with PROC REPORT, but not directly with PROC PRINT. I gather that you have already calculated the PERCNTUSED value?? But in order to do what you want, you would have to rewrite or redesign your whole batch job one way or another.

cynthia

Proc Print Question

garybald — Mon, 01 Aug 2011 19:56:55 GMT

A little bit of sql would keep the rewriting to a minimum. Put code like this before your proc print;

proc sql;

create table new as

select *, sum(cap) as capsum, sum(alloc) as allocsum

from new;

proc sql;

create table new as

select *, ((allocsum / capsum) * 100) as percent

from new;

proc print;

run;

Proc Print Question

art297 — Mon, 01 Aug 2011 20:42:41 GMT

If you don't have any deleted records in file new, and can get away with an extra column, you might be able to get away with something like:

data _new ;

set new nobs=recs;

pct=100/recs*alloc/cap;

run;

PROC PRINT DATA = _NEW NOOBS;

sum CAP ALLOC AVAL pct;

VAR SUBSYS CAP ALLOC pct AVAL PERCNTUSED;

run;

Re: Proc Print Question

MikeZdeb — Mon, 01 Aug 2011 20:49:13 GMT

Hi ... there was a PROC SQL suggestion, so ...

You use PROC SQL just like PROC PRINT, just don't create a table.

Since one of the values of AVAL ends in a zero, a format (6.2) is

used to assure two decimal places in the table (the width also accomodates the SUM).

Otherwise ...

select * , 100*alloc/cap as PERCENT format=4.1 from x

would suffice. PROC SQL uses the variable names as entered, so

they are all in CAPS.

data x;

input SUBSYS : $6. CAP ALLOC AVAL;

datalines;

2107#1 67.94 49.26 18.68

2107#2 67.94 50.93 17.01

2107#3 69.39 39.54 29.85

2107#4 67.24 24.94 42.30

;

run;

proc sql;

select SUBSYS, CAP, ALLOC, AVAL format=6.2, 100*alloc/cap as PERCENT format=4.1 from x

union all

select 'TOTAL' as subsys, sum(cap), sum(alloc) , sum(aval), 100*sum(alloc)/sum(cap) from x;

quit;

SUBSYS CAP ALLOC AVAL PERCENT

-------------------------------------------

2107#1 67.94 49.26 18.68 72.5

2107#2 67.94 50.93 17.01 75.0

2107#3 69.39 39.54 29.85 57.0

2107#4 67.24 24.94 42.30 37.1

TOTAL 272.51 164.67 107.84 60.4

Re: Proc Print Question

art297 — Mon, 01 Aug 2011 21:24:20 GMT

Mike,

I like your suggestion better than the one I proposed but, since the OP already has PERCNTUSED in his dataset, I would simplify it one step further with something like:

proc sql;

select SUBSYS format=$8., CAP, ALLOC, AVAL format=6.2,

PERCNTUSED format=4.1

from new

union all

select 'TOTAL' as subsys, sum(cap), sum(alloc),

sum(aval), sum(PERCNTUSED)/count(cap)

from new;

quit;

Re: Proc Print Question

Howles — Mon, 01 Aug 2011 22:06:26 GMT

I nominate PROC TABULATE.

proc tabulate data=x ;

class subsys ;

var CAP ALLOC AVAL ;

table subsys all=''

(CAP ALLOC AVAL)*mean=''

ALLOC='PERCNTUSED'*pctsum<CAP>=''*f=10.1

;

run ;

MikeZdeb wrote:

Hi ... there was a PROC SQL suggestion, so ...

You use PROC SQL just like PROC PRINT, just don't create a table.

Since one of the values of AVAL ends in a zero, a format (6.2) is

used to assure two decimal places in the table (the width also accomodates the SUM).

Otherwise ...

select * , 100*alloc/cap as PERCENT format=4.1 from x

would suffice. PROC SQL uses the variable names as entered, so

they are all in CAPS.

data x;

input SUBSYS : $6. CAP ALLOC AVAL;

datalines;

2107#1 67.94 49.26 18.68

2107#2 67.94 50.93 17.01

2107#3 69.39 39.54 29.85

2107#4 67.24 24.94 42.30

;

run;

proc sql;

select SUBSYS, CAP, ALLOC, AVAL format=6.2, 100*alloc/cap as PERCENT format=4.1 from x

union all

select 'TOTAL' as subsys, sum(cap), sum(alloc) , sum(aval), 100*sum(alloc)/sum(cap) from x;

quit;

SUBSYS CAP ALLOC AVAL PERCENT

-------------------------------------------

2107#1 67.94 49.26 18.68 72.5

2107#2 67.94 50.93 17.01 75.0

2107#3 69.39 39.54 29.85 57.0

2107#4 67.24 24.94 42.30 37.1

TOTAL 272.51 164.67 107.84 60.4

Proc Print Question

Nooby1Konoby — Tue, 02 Aug 2011 11:56:20 GMT

Thank you all for the help, Proc SQL was the way to go. Again thank you all very much for the guidance.

Cheers

Tim

Proc Print Question

Nooby1Konoby — Tue, 02 Aug 2011 13:01:31 GMT

Ok one quick question Mike, I used what you suggested and it worked perfect, i was courious if there was a way to seperate the totals with a line above them?

Re: Proc Print Question

Cynthia_sas — Tue, 02 Aug 2011 14:19:58 GMT

Hi:

I'm not sure that PROC SQL will do the lines. However, PROC REPORT is still my choice for accomplishing this. See the screen shot of the LISTING and HTML output created by the program shown below.

cynthia

data subsys;
infile datalines dlm=' ';
input SUBSYS $ CAP ALLOC AVAL PERCNTUSED;
return;
datalines;
2107#1          67.94     49.26     18.68       72.5
2107#2          67.94     50.93     17.01       75.0
2107#3          69.39     39.54     29.85       57.0
2107#4          67.24     24.94     42.30       37.1
;
run;

ods listing;

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

proc report data=work.subsys nowd split='*';
title1 'Title1';
column SUBSYS CAP ALLOC AVAL PERCNTUSED;
define subsys / order 'Sub*Sys';
define cap / sum 'Cap';
define alloc / sum 'Alloc';
define aval / sum 'Avail';
define percntused / sum f=percent9.1
         'Percent*Used';
rbreak after / summarize dol;
compute percntused;
    ** divide regular percntused by 100 so percent format;
    ** can be used to add % to number;
    ** at rbreak, do division of alloc by cap;
    ** no need to multiply by 100 because the percent format;
    ** will do that;
    if _break_ = ' ' then
       percntused.sum = percntused.sum / 100;
    else if _break_ = '_RBREAK_' then
       PERCNTUSED.sum = (alloc.sum / cap.sum);
endcomp;
run;

ods _all_ close;

Re: Proc Print Question

Nooby1Konoby — Tue, 02 Aug 2011 14:24:46 GMT

Thanks Cynthia, appreciate the help!

Cheers

Tim

Re: Proc Print Question

MikeZdeb — Tue, 02 Aug 2011 15:32:07 GMT

Hi ... here's a way to add a blank line. The OPTIONS MISSING is used to keep periods out of that blank line

in the columns for the numeric variables. You'll get a WARNING in the LOG, ...

WARNING: A table has been extended with null columns to perform the UNION ALL set operation

Image of HTML output is attached.

data x;

input SUBSYS : $6. CAP ALLOC AVAL;

datalines;

2107#1 67.94 49.26 18.68

2107#2 67.94 50.93 17.01

2107#3 69.39 39.54 29.85

2107#4 67.24 24.94 42.30

;

run;

ods listing close;

ods results off;

ods html file='z:\table.html' style=barrettsblue;

options missing=' ';

proc sql;

select SUBSYS, CAP, ALLOC, AVAL format=6.2, 100*alloc/cap as PERCENT format=4.1 from x

union all

select distinct ' ' as subsys from x

union all

select 'TOTAL' as subsys, sum(cap), sum(alloc) , sum(aval), 100*sum(alloc)/sum(cap) from x;

quit;

options missing='.';

ods html close;

ods results;

ods listing;

Re: Proc Print Question

Ksharp — Thu, 04 Aug 2011 09:14:52 GMT

I nominated data step, since you only want proc print.

data subsys;
  infile datalines dlm=' ';
  input SUBSYS $ CAP ALLOC AVAL PERCENTUSED;
return;
datalines;
2107#1          67.94     49.26     18.68       72.5  
2107#2          67.94     50.93     17.01       75.0  
2107#3          69.39     39.54     29.85       57.0  
2107#4          67.24     24.94     42.30       37.1  
;
run;
data want(drop=cap_sum alloc_sum aval_sum) ;
 set subsys end=last;
 cap_sum+cap;alloc_sum+alloc; aval_sum+aval;
 output;
 if last then do;
                subsys='Total';
                cap=cap_sum;
                alloc=alloc_sum;
                aval=aval_sum;
                percentused=100*alloc_sum/cap_sum ;
                output;
               end;
run;
proc format;
 picture fmt
   low - high ='09.9%';
run;
proc print data=want noobs;
 format percentused fmt.;
run;

Ksharp