BookmarkSubscribeRSS Feed
Nooby1Konoby
Calcite | Level 5

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;

12 REPLIES 12
Cynthia_sas
SAS Super FREQ

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

garybald
Calcite | Level 5

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;

art297
Opal | Level 21

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;

MikeZdeb
Rhodochrosite | Level 12

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


art297
Opal | Level 21

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;

Howles
Quartz | Level 8

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


Nooby1Konoby
Calcite | Level 5

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?

MikeZdeb
Rhodochrosite | Level 12

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
Nooby1Konoby
Calcite | Level 5

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

Cheers

Tim

Cynthia_sas
SAS Super FREQ

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
Nooby1Konoby
Calcite | Level 5

Thanks Cynthia, appreciate the help!

Cheers

Tim

Ksharp
Super User

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

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
  • 12 replies
  • 2041 views
  • 6 likes
  • 7 in conversation