Comparing Procedures That Produce Detail Reports

Overview

When you want to produce a detail report, you can choose between the PRINT procedure and the SQL procedure:
Procedure
Description
PROC PRINT
  • can calculate column sums
PROC SQL
  • can manipulate data and create a SAS data set in the same step that creates the report
  • can calculate column and row statistics
To perform a particular task, a single-purpose tool like PROC PRINT generally uses fewer computer resources than a multi-purpose tool like PROC SQL.
To illustrate the differences in resource usage between PROC PRINT and PROC SQL, consider some sample queries.

Example: Using PROC PRINT and PROC SQL to Create Detail Reports

Suppose you are working with the data set Company.Products and you want to generate four types of detail reports:
  • simple detail report
  • subset detail report
  • sorted detail report
  • sorted subset detail report
For the first three reports, the PROC PRINT program is likely to use fewer resources than the PROC SQL program. For the last report, the resource usage for the two programs is likely to be about the same.

Report 1: Simple Detail Report

The simple detail report lists the product ID, product name, and supplier name for all products. The PROC PRINT program and PROC SQL program for producing this report are shown below:
PROC PRINT
PROC SQL
 
proc print data=company.products;
   var product_id product_name
       supplier_name;
run;
 
proc sql;
   select product_id product_name
          supplier_name
      from company.products;
quit;
In this situation, the PROC PRINT program is likely to use fewer CPU and memory resources than the PROC SQL program. The I/O resource usage should be approximately the same.

Report 2: Subset Detail Report

The subset detail report lists the product ID, product name, and supplier name for all products that come from Sweden (SE). The PROC PRINT program and PROC SQL program for producing this report are shown below:
PROC PRINT
PROC SQL
 
proc print data=company.products;
   var product_id product_name
       supplier_name;
   where supplier_country='SE';
run;
 
proc sql;
   select product_id product_name
          supplier_name
      from company.products
      where supplier_country='SE';
quit;
Both steps use WHERE processing to subset the data. In this situation, the PROC PRINT program is likely to use fewer CPU and memory resources than the PROC SQL program. The I/O resource usage should be approximately the same.

Report 3: Sorted Detail Report

The sorted detail report lists the product ID, product name, and supplier name for all products, with observations that are sorted by the supplier country. The PROC PRINT program and PROC SQL program for producing this report are shown below:
PROC PRINT
PROC SQL
 
proc sort data=company.products
     out=product;
   by supplier_country;
run;

proc print data=product;
   var product_id product_name
       supplier_name;
run;
 
proc sql;
   select product_id product_name
          supplier_name
      from company.products
      order by supplier_country;
quit;
To sort the data, a PROC SORT step has been added to the PROC PRINT program, and an ORDER BY clause has been added to the PROC SQL program. In this situation, the PROC PRINT program is likely to use fewer CPU and memory resources than the PROC SQL program. The I/O resource usage should be approximately the same.

Report 4: Sorted Subset Detail Report

The sorted subset detail report lists the product ID, product name, and supplier name for all products that come from Sweden (SE), with observations that are sorted by the supplier name. The PROC PRINT program and PROC SQL program for producing this report are shown below:
PROC PRINT
PROC SQL
 
proc sort data=company.products
     (keep=Product_ID Product_Name
     Supplier_Name Supplier_Country)
     out=product;
     where supplier_country='SE';
   by supplier_name;
run;

proc print data=product;
   var product_id product_name
       supplier_name;
run;
 
proc sql;
   select product_id product_name
          supplier_name
      from company.products
      where supplier_country='SE'
      order by supplier_name;
quit;
To sort the data, a PROC SORT step has been added to the PROC PRINT program. The PROC SORT step uses the KEEP= option to subset the observations, which improves efficiency. The PROC SQL step uses an ORDER BY clause for sorting and a WHERE clause for subsetting. In this situation, the CPU and memory usage for the PROC PRINT program and the PROC SQL program are about the same.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.138.123.106