Help using Base SAS procedures

Proc Print Question

Reply
Occasional Contributor
Posts: 16

Proc Print Question

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;

SAS Super FREQ
Posts: 8,868

Proc Print Question

Posted in reply to Nooby1Konoby

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

Contributor
Posts: 29

Proc Print Question

Posted in reply to Cynthia_sas

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 Star
Posts: 7,487

Proc Print Question

Posted in reply to Nooby1Konoby

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;

Valued Guide
Posts: 765

Re: Proc Print Question

Posted in reply to Nooby1Konoby

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 Star
Posts: 7,487

Re: Proc Print Question

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;

Regular Contributor
Posts: 184

Re: Proc Print Question

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


Occasional Contributor
Posts: 16

Proc Print Question

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?

Valued Guide
Posts: 765

Re: Proc Print Question

Posted in reply to Nooby1Konoby

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;





table.jpg
Occasional Contributor
Posts: 16

Proc Print Question

Posted in reply to Nooby1Konoby

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

Cheers

Tim

SAS Super FREQ
Posts: 8,868

Re: Proc Print Question

Posted in reply to Nooby1Konoby

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;


listing_html_report_percent.jpg
Occasional Contributor
Posts: 16

Re: Proc Print Question

Posted in reply to Cynthia_sas

Thanks Cynthia, appreciate the help!

Cheers

Tim

Super User
Posts: 10,041

Re: Proc Print Question

Posted in reply to Nooby1Konoby

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

Ask a Question
Discussion stats
  • 12 replies
  • 581 views
  • 6 likes
  • 7 in conversation