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.
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.