Help using Base SAS procedures

Adding a Formula on SAS

Posts: 0

Adding a Formula on SAS

Hi All,

I am a new SAS user and this is my 1st post on this forum.

I would like to know if I can input formulas in to a SAS code to compute a %.

An example is, say I have 120 accounts, 50 from the said 120 are over a certain number of days old.

How would I get SAS to compute the 50 accounts as a % of the total.

Manual way of doing this would be 50/120*100=41.66%

Any ideas on this would be much appreciated.
Frequent Contributor
Posts: 106

Re: Adding a Formula on SAS

Welcome to the world of SAS!

Yes you can. SAS has a very rich set of functions and formats allowing you to transform and output data in almost any way you might imagine.

Mostly one will use a format to output the value 0.4166 as 41.66%. Sample follows. I've added two lines that display both the operating system one is using as the SAS version respectively. I think it is good practice to add these two pieces of information whenever one posts a question/problem to audiences like this forum.

So here goes:
data _null_ ;
acct_total = 120 ;
acct_over = 50 ;
over_percentage = acct_over / acct_total ;
put "Operating System: &SYSSCPL"
/ "SAS Version: %sysfunc(hotfix())"
/ 22*'- '
/ 'using format 6.2' @20 over_percentage 6.2
/ 'using percent8.2' @20 over_percentage percent8.2
/ 22*'- '
stop ;
run ;
Output looks like this (for me, a die-hard mainframer 8) )
Operating System: z/OS
SAS Version: SAS 9.1.3 Service Pack 4
- - - - - - - - - - - - - - - - - - - - - -
using format 6.2 0.42
using percent8.2 41.67%
- - - - - - - - - - - - - - - - - - - - - -
Hope this helps for a start
Posts: 8,743

Re: Adding a Formula on SAS

Although you could write your own formula in a program, SAS has procedures that will calculate percentages for you. So, you don't really need to "input a formula" as you would in a spreadsheet program, for example -- you just need to decide which procedure you want to use with your data.

To use a SAS procedure, your data must be in SAS data set format -- either you already have a SAS dataset or you will be reading data (from Excel, from a comma separated file, from a tab delimited file, or ...) into SAS data set format.

Once you have yout data in SAS data set format, then you would use a SAS procedure to create your desired report. For example if you wanted simple frequency counts and percents, then you might use PROC FREQ; if you wanted to have a bit more control over the structure of the report table, then you might use PROC TABULATE or PROC REPORT. If you needed statistical analysis, then you might use one of the SAS/STAT procedures, such as PROC ANOVA or PROC REG (to name just a few).

In the program below, I illustrate these points in a multi-step program. The first step creates a SAS dataset from comma-delimited data. Then, the LISTING destination is closed and the HTML destination is opened. The report file created by ODS HTML will be named c:\temp\show_percent.html and the style of the report will be black text on a white background with blue headers (which are all defined in the SASWEB style).

Inside the ODS HTML "sandwich" statements: ODS HTML FILE= ... ODS HTML CLOSE; are a series of procedure steps. Each procedure step starts with the keyword PROC and is followed by the name of the procedure that is being used.

Each procedure needs to know the name of the data set that is the input to the procedure -- so the DATA= option provides the name of the SAS data set (ACCT) that was created in step 1.

For now, without going into detail on the specifics of each procedure, let's just say that you will be comparing the output from one PROC FREQ step (Report #1) and 5 PROC TABULATE steps (Report #2 - Report #6). The PROC FREQ step produces 3 report tables; the PROC TABULATE steps produce 1 table each.

There is a lot of good documentation and tutorials that can help you get started with SAS programming. You also will need to understand the method by which your SAS programs will be submitted -- for example -- are you using SAS Enterprise Guide to submit programs to SAS on a server? Are you using SAS on a mainframe computer using Job Control Language or using Roscoe or Wylbur? Are you using SAS on UNIX or are you using SAS Learning Edition?

To learn something about SAS datasets and SAS variables and the basics of SAS programming, you may want to read some of these documentation topics: This topic is composed of the following subtopics:
Components of Base SAS Software
Output Produced by the SAS System
Ways to Run SAS Programs
Running Programs in the SAS Windowing Environment
Review of SAS Tools This topic is on the Fundamental Concepts of Using SAS Procedures and is composed of the following sub-topics
Language Concepts
Procedure Concepts
Output Delivery System

The program below should be cut and pasted from the forum posting mechanism into a word processor like Microsoft Word (to preserve the line feeds); then cut and pasted from Word into the editor you use for your SAS programs. You can then submit the program and since it builds the data and runs the procedures, the program should run successfully and then you can review, change and rerun the program as you study the basics of SAS and learn more about these procedures. For comparison purposes, there are exactly 120 observations or rows in the data set. Exactly 50 of those observations have a value of 4 for the DAYS_OLD variable or column. Also, exactly 50 observations or rows have the value of Region 2 for REGION. My data includes the SALES variable so you can compare the difference between the PCTN statistic and the PCTSUM statistic.

** Make some data;
data acct(keep=acctnum region sales days_old );
infile datalines dlm=',' dsd;
input acctnum region $ sales days_old;
3092000,"Region 1",3033,4
3290000,"Region 1",3230,4
3080000,"Region 1",3019,4
5451000,"Region 1",5389,4
6078100,"Region 1",60712,7
1181800,"Region 1",11754,4
1164040,"Region 1",116333,7
5046000,"Region 1",4978,6
1490800,"Region 1",149013,4
1005000,"Region 1",937,4
2051800,"Region 1",20448,6
7831000,"Region 1",78234,7
1226000,"Region 1",1155,4
2054000,"Region 1",1996,4
2976400,"Region 1",29761,6
6724800,"Region 1",67242,4
7680300,"Region 1",76793,7
6283300,"Region 1",62819,8
6865000,"Region 1",68641,4
1700000,"Region 1",1690,4
5155000,"Region 2",51541,6
1089520,"Region 2",108942,6
2130700,"Region 2",21297,4
6322000,"Region 2",63206,4
1237570,"Region 2",123743,6
2921500,"Region 2",29198,4
6491100,"Region 2",64891,7
2640000,"Region 2",2617,8
9066500,"Region 2",90648,6
4872000,"Region 2",4846,8
3602310,"Region 2",360209,4
4074000,"Region 2",4051,4
1056000,"Region 2",10532,8
1376100,"Region 2",13732,8
2283000,"Region 2",2259,6
3285000,"Region 2",328474,4
1412100,"Region 2",14095,6
8393000,"Region 2",8365,4
1736500,"Region 2",17337,6
3948000,"Region 2",39452,6
5207000,"Region 2",5172,8
4271400,"Region 2",42682,4
1931500,"Region 2",19282,4
9275000,"Region 2",9244,4
1808700,"Region 2",18053,6
2646700,"Region 2",26427,8
4348600,"Region 2",43452,4
2562000,"Region 2",2521,7
1961800,"Region 2",19582,4
4807300,"Region 2",48031,6
1396400,"Region 2",13921,6
5773400,"Region 2",57691,4
1671100,"Region 2",16662,8
5284800,"Region 2",52807,4
4939000,"Region 2",4888,8
1796200,"Region 2",17919,4
3297400,"Region 2",32928,6
6133000,"Region 2",6081,8
6294100,"Region 2",62893,6
2963000,"Region 2",29582,6
1120100,"Region 2",11145,8
1919900,"Region 2",19146,4
8520000,"Region 2",801,6
8518000,"Region 2",8467,4
1633800,"Region 2",16282,4
8640000,"Region 2",8587,4
1635100,"Region 2",16289,8
3501100,"Region 2",34955,6
2267000,"Region 2",2202,8
2857400,"Region 2",28515,6
1779900,"Region 3",17720,8
1284800,"Region 3",12775,4
2966000,"Region 3",2886,7
5752000,"Region 3",5676,6
9829000,"Region 3",9745,8
1267900,"Region 3",12601,6
4029500,"Region 3",40213,4
5401000,"Region 3",53929,6
1120890,"Region 3",112009,4
3089000,"Region 3",3002,7
1353900,"Region 3",135305,4
2951800,"Region 3",29435,4
2458200,"Region 3",24497,6
1327240,"Region 3",132638,6
7982000,"Region 3",7892,4
1929700,"Region 3",19210,4
2689000,"Region 3",2600,6
3100300,"Region 3",30905,8
2693000,"Region 3",2598,6
1880300,"Region 3",18712,4
3391900,"Region 3",33824,4
3338900,"Region 3",33291,6
1549800,"Region 3",15403,6
3761500,"Region 3",37519,6
1287000,"Region 3",1190,6
8045200,"Region 3",80352,4
3469100,"Region 3",34585,8
6359400,"Region 3",63492,4
5404600,"Region 3",53940,7
2865980,"Region 3",286497,4
3534670,"Region 3",353361,4
7579080,"Region 3",757798,8
5228000,"Region 3",5120,4
7006240,"Region 3",700513,7
6413700,"Region 3",64026,6
3042220,"Region 3",304106,8
7564550,"Region 3",756347,4
1024830,"Region 4",102372,6
5762290,"Region 4",576112,8
2709760,"Region 4",270863,6
2002720,"Region 4",200156,4
4767570,"Region 4",476638,7
1829300,"Region 4",18177,6
2151910,"Region 4",215069,8
3759330,"Region 4",375817,4
3478300,"Region 4",34660,7
6939600,"Region 4",69276,6
3551500,"Region 4",35393,4
6560400,"Region 4",65478,7
9266400,"Region 4",92539,4

** Show the difference between PROC FREQ percentages and;
** PROC TABULATE percent of N (COUNT) and percent of SUM of SALES;
ods listing close;
ods html file='c:\temp\show_percent.html' style=sasweb;
proc freq data=acct;
title '1) Percentage and Crosstab in PROC FREQ';
tables days_old region days_old*region;

proc tabulate data=acct f=comma9.;
title '2) Percent of N in PROC TABULATE (no decimals)';
class days_old;
table days_old all,
n pctn;

proc tabulate data=acct f=comma9.;
title '3) Percent of N in PROC TABULATE (decimal places)';
class days_old;
table days_old all,
n pctn*f=8.3;

proc tabulate data=acct f=comma9.;
title '4) Percent of Sales Sum in PROC TABULATE';
class days_old;
var sales;
table days_old all,
sales*(sum pctsum*f=8.3) all*sales*(sum pctsum*f=8.3);

proc tabulate data=acct f=comma9.;
title '5) CrossTAB N and PCTN with Days_Old and Region in PROC TABULATE';
class region days_old;
var sales;
table days_old all,
region*(n pctn*f=8.3) all*(n pctn*f=8.3);

proc tabulate data=acct f=comma9.;
title '6) CrossTAB SUM and PCTSUM with Days_old and Region and Sales in PROC TABULATE';
class region days_old;
var sales;
table days_old all,
region*sales*(sum pctsum*f=8.3) all*sales*(sum pctsum*f=8.3);
ods html close;
Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation