Why SAS programmers love SAS

0

SAS provides solutions that have analytics integrated into reports that deliver insight into what will happen in the future, not just report on what has happened in the past.  Who wouldn't love that? However, what makes the SAS programming environment so useful to end users is how efficient and effective it is with working with data.   We have all heard "a picture is worth a thousand works" as a result here is just ONE example that shows why SAS programmers love SAS.

Fair warning, there's code after the jump, but you don't have to be a programmer to understand my point.

/* Four lines of SAS code.   SAS has thousands of Procedures or PROCs with similar lines of code.  */
proc rank data=indb2.db2_order_item out=work.order descending ties=low;
   var quantity product_id;
   ranks QuantityRank ProductRank;
run;

/* Here is just SOME of the equivalent SQL. Why only some? Because I couldn't fit more on a PowerPoint. */

WITH "subquery0" ( "COSTPRICE_PER_UNIT", "DISCOUNT", "ORDER_ID", "ORDER_ITEM_NUM","PRODUCT_ID", "QUANTITY", "TOTAL_RETAIL_PRICE" ) AS ( SELECT "COSTPRICE_PER_UNIT", "DISCOUNT", "ORDER_ID", "ORDER_ITEM_NUM", "PRODUCT_ID", "QUANTITY", "TOTAL_RETAIL_PRICE" FROM "DB2_ORDER_ITEM" ) SELECT "table0"."ORDER_ID", "table0"."ORDER_ITEM_NUM", "table0"."PRODUCT_ID", "table0"."QUANTITY", "table0"."TOTAL_RETAIL_PRICE", "table0"."COSTPRICE_PER_UNIT", "table0"."DISCOUNT", "table2"."rankalias1" AS "QUANTITYRANK", "table1"."rankalias0" AS "PRODUCTRANK" FROM "subquery0" AS "table0" LEFT JOIN ( SELECT DISTINCT "PRODUCT_ID", "tempcol0" AS "rankalias0" FROM ( SELECT "PRODUCT_ID", MIN( "tempcol1" ) OVER (PARTITION BY "PRODUCT_ID" ) AS "tempcol0" FROM ( SELECT "PRODUCT_ID", CAST( ROW_NUMBER() OVER (ORDER BY "PRODUCT_ID" DESC ) AS DOUBLE PRECISION ) AS "tempcol1" FROM "subquery0" WHERE ( ("PRODUCT_ID" IS NOT NULL ) ) ) AS "subquery2" ) AS "subquery1" ) AS "table1" ON ( ("table0"."PRODUCT_ID" = "table1"."PRODUCT_ID" ) ) LEFT JOIN ( SELECT DISTINCT "QUANTITY", "tempcol2" AS "rankalias1" FROM ( SELECT "QUANTITY", MIN( "tempcol3" ) OVER ( PARTITION BY "QUANTITY" ) AS "tempcol2" FROM ( SELECT "QUANTITY", CAST( ROW_NUMBER() OVER ( ORDER BY "QUANTITY" DESC ) AS DOUBLE PRECISION ) AS "tempcol3" FROM "subquery0" WHERE ( ( "QUANTITY" IS NOT NULL ) ) ) AS "subquery4" ) AS "subquery3" ) AS "table2" ON ( ( "table0"."QUANTITY" = "table2"."QUANTITY" ) )

If you need to make an argument for SAS in your workplace, having this as a build in PowerPoint really drives the point home.

Finally, I will ask you: Which piece of code would you like to maintain, especially if something changes? Because, as you know, something always does.

Share

About Author

David Pope

Technical Leader, Senior Manager US Energy

David leads the pre-sales technical team for SAS US Energy which solves business problems in the Oil & Gas and Utilities industries using advanced analytics. He earned a BS in Industry Engineering and a Computer Programming Certificate from North Carolina State University. Furthermore, he has over 27 years of business experience working with SAS across R&D, IT, Sales and Marketing in the Americas and Europe. He is an expert in working with data and producing insights through the use of analytics. David has presented at SAS Global Forum, the 2012 SAS Government Leadership Summit, IBM’s Information on Demand(IOD), EMC World, CTO Summit Conferences, is the author of the book: "Big Data Analytics with SAS", and he currently holds 11 patents for SAS in several countries: US, CA, Norway, UK, China, and Hong Kong.

Leave A Reply

Back to Top