SAS Tips Techniques

Document Sample
SAS Tips  Techniques Powered By Docstoc
					                           Montreal SAS User Group




SAS Tips & Techniques
 Prepared by: Luigi Muro – Consultant
              Bell Canada




           www.monsug.ca
                                             Tips & Techniques

Tip # 1: Optimization of Data Step

GOAL: Reduce the number of data steps required to perform a task.


  In Create a 1 Flat file containing samples from 2 SAS files. put Files
  Statistics:
  INPUT1       Number of Records:       2.7 Millions
               Number of Variables: 52
               Variable Length:         257

  INPUT2       Number of Records:              4.2 Millions
               Number of Variables:            52
               Variable Length:                257




                     Montreal SAS User Group - www.monsug.ca               2
                                               Tips & Techniques

Solution 1 (Disk Space Issues)                 Data Temp3;
                                                Set Temp1 Temp2;
                                               Run;
Data Temp1;
 Set input1.indices;
 choose=ranbin(0,1, 0.01);                     Data Temp3;
Run;                                            Set Temp3;
                                                file output;
Data Temp1;                                     put Var1 „;‟
 Set Temp1;                                          Var2 „;‟
 Where choose=1;                                     Var3 „;‟
Run;                                                 …
                                                     VarN ;
Data Temp2;                                    Run;
 Set input2.indices;
 choose=ranbin(0,1, 0.025);
Run;                                          Read and Create 6 SAS datasets!

Data Temp2;
 Set Temp2;
 Where choose=1;
Run;
                                        
                        Montreal SAS User Group - www.monsug.ca                 3
                                               Tips & Techniques
Solution 2 (More Efficient, reduce the number of steps)

Data _Null_ ;
 Set input1.indices(Keep=VAR: where=(ranbin(0, 1, 0.01)))
     input2.indices(Keep=VAR: where=(ranbin(0, 1, 0.25))) ;
 File output delimiter=”;”;
 format var: 8.6;
 Put … ;
Run ;

Note: No output file is created. (Not needed since the required output is
      a flat file)

Version 6 (Delimiter option not available) : Put (var:) (8.6,';') ;


                        Montreal SAS User Group - www.monsug.ca             4
                                         Tips & Techniques

Tip # 2: Use a SAS procedure to replace many Data Steps
GOAL: Produce a sales report: Percent sales by product.

   Input: SAS Dataset                           Output: Tabular Report
ProdCd        Amount                           Monthly Sales by Product
PROD1         20.21
                                            Product         Sales   Percent
PROD2         23.01
                                             PROD1          40.42   31.74%
PROD3         10.50
PROD4         15.21                          PROD2          46.02   36.13%
PROD1         20.21                          PROD3          10.50   8.24%
PROD2         23.01                          PROD4          20.42   23.89%
PROD4         15.21
Total Sales   127.36

                  Montreal SAS User Group - www.monsug.ca                     5
                                             Tips & Techniques

     Solution 1 (DATA STEP)                Proc Print Data=Percent;
                                            Title “Percent sales by product”;
Proc Means Data=sales Noprint;              Var Product TotalSales PercentSales;
 Class Product;                            Run;
 Var SalesAmount;
 Output Out=Totals Sum=TotalSales;
Run;                                       ---------------------------------------------------------

                                                   Percent sales by product
Data Percent;
 Retain GrandTot;
                                           Product          TotalSales        PercentSales
 Set Totals;                                 PROD1              40.42              31.74
 if _n_ = 1 then do;                         PROD2              46.02              36.13
   GrandTot=TotalSales;                      PROD3              10.50               8.24
   delete;                                   PROD4              30.42              23.89
 end;                                      ---------------------------------------------------------
 PercentSales=TotalSales/GrandTot;
 format PercentSales percent8.2;
Run;

                      Montreal SAS User Group - www.monsug.ca                                          6
                                             Tips & Techniques

    Solution 2 (PROC FREQ)
                                                         Proc FREQ output

Proc Freq Data=Sales;                      Product        Frequency    Percent
 Tables Product / nocum;                   PROD1            40.42       31.74
 Weight SalesAmount;                       PROD2            46.02       36.13
Run;                                       PROD3            10.50        8.24
                                           PROD4            30.42       23.89


The nocum option removes the
Cumulative Frequency and Percent.



The weight statement multiplies each
occurrence of PRODUCT by the variable
SalesAmount.



                      Montreal SAS User Group - www.monsug.ca                    7
                                              Tips & Techniques
Tip # 3: Send an E-mail from SAS (SAS V8)

filename doemail email
   to=('joe.doe@bell.ca' 'jane.doe@bell.ca')
   cc='gino.tremblay@sympatico.ca'
   subject='New Transaction File. '
   attach='Filename.xls';

data _null_;
 file doemail;
 put 'Please note that a new transaction file is available';
run;

Note: the following statements must be added to Sasv8.cfg or at invocation
-emailsys SMTP or MAPI
-emailhost “your.smtp_server.host” (mailserver.bell.ca)


                       Montreal SAS User Group - www.monsug.ca               8
                                        Tips & Techniques
Tip # 4 Passing parameters to SAS with SYSPARM option.

MVS – (Batch)

//RUNSAS EXEC SAS,OPTIONS=“SYSPARM=QUE”
//SYSIN DD *
 %let province = &sysparm;



Unix/Windows Batch session
SAS -sysin ‘pgm.sas’ -log ‘pgm.log’ -sysparm ‘QUE’


 Data _null_;
  Province = “&sysparm”;
 Run;


                 Montreal SAS User Group - www.monsug.ca    9
                                             Tips & Techniques
Tip # 5. Dates and Strings Manipulations
GOAL: List of working days for next week (Exclude Sat. and Sun.).

Data _null_;
LENGTH datestr $50;
DateStr=„[';

DO Dt = (TODAY()) TO (TODAY() + 6) by 1;
 IF (1 LT WEEKDAY(Dt) LT 7)
  THEN DateStr=TRIM(DateStr) || PUT(Dt,Date7.) !! ',';
END;

SUBSTR(DateStr,LENGTH(TRIM(DateStr)),1)=„]';
CALL SYMPUT(„Datestr',TRIM(DateStr));
STOP;
Run;

&Datestr contains [12NOV03,13NOV03,14NOV03,17NOV03,18NOV03]
                      Montreal SAS User Group - www.monsug.ca       10
                                        Tips & Techniques
Tip # 6 Use SQL procedure to simplify Merge.
GOAL: Merge 3 SAS data sets that do not have a common BY
      variable.



orders                  prices                             customers
PartNumber              PartNumber                         Company
Quantity                UnitPrice                          Address
CustomerID                                                 City
                                                           State
                                                           CustomerID
                 Montreal SAS User Group - www.monsug.ca                11
                                         Tips & Techniques

Solution # 1 (Many Data Steps)         Sort Customers by CustomerID
                                        if needed.
Sort Orders and Prices,
by PartNumber.                         Data step2;
                                         merge step1(in=inStep1)
                                               Customers;
Data step1;
                                         by CustomerID;
  merge Orders(in=Orders)                if inStep1;
        Prices;                          Sale=Quantity*UnitPrice;
  by PartNumber;                       Run;
  if Orders;
Run;                                   proc sort data=step2;
                                          by Company;
Proc sort data=step1;
   by CustomerID;                      proc print data=step2;
                                          var CustomerID …;
Run;
                                       run;

                  Montreal SAS User Group - www.monsug.ca             12
                                           Tips & Techniques
Solution # 2 (Proc SQL)

proc sql;

   select   a.CustomerID,
            Company,
            Address,
            b.PartNumber,
            Quantity,
            Quantity*UnitPrice as Sale

        from Customers as a,
             Prices as b,
             Orders as c

     where a.CustomerID = c.CustomerID and
           b.PartNumber = c.PartNumber

     order by Company        ;

quit;

                    Montreal SAS User Group - www.monsug.ca    13
                                             Tips & Techniques

Tip # 7a – Use SAS Views to Optimize process (Input views)

Data Step (NO View)                         Data Step (WITH View)


Data TempFile;                              Data TempFile / View=TempFile;
 set in.PermFile;                            set in.PermFile;
 if prov = „Q‟ then Amt * .40;               if prov = „Q‟ then Amt * .40;
               else Amt * .60;                             else Amt * .60;
Run;                                        Run;
Proc print data=TempFile;                   Proc print data=TempFile;


SAS Dataset TempFile created only for
the purpose of creating a temporay
variable.




                      Montreal SAS User Group - www.monsug.ca                14
                                             Tips & Techniques

Tip # 7b – Use SAS Views to Optimize process (Output views)

Data _null_ / view=file3;
 length field1 $ 10 field2 $10
 acct $20 balance 8 . . .;
 set file3;
 File OUT;
 Put var1 var2… ;
run;


proc sort data=file1 out=file3;
 by balance;
run;


                      Montreal SAS User Group - www.monsug.ca    15

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:147
posted:1/18/2009
language:English
pages:15
The Slasher The Slasher
About