proc sql having clause pivot table

blakezen — Thu, 21 Jul 2016 15:10:14 GMT

Hi All,

I am trying to use Proc Sql to basically do the equivalent of a pivot table that can be done using excel. I have a dataset with a number of columns and row that I import into sas. For completeness, I have transcribed it using datalines and input below. Following my code is the result. But it does not match with the desired output. The code does not do the filter and aggregation properly. My desired output (done on excel) is at the end.

Any help you can provide to fix my broke code and improve any steps for efficiency is much appreciated. Thank you so much!

Code:

data have;
input namel $ endbal netof accst $ balsheet$ lien_position $ impairec $ lien_pp rep_cat $;
datalines;
am2 1000 1000 active he first yes 1 he1
am3 2000 2000 active he second no 2 he1
am4 1415 1100 active fm first no 1 fm1
am5 1600 1000 active fm second no 2 fm2
am5 2200 900 active he first yes 1 he2
am6 3000 2000 active he second yes 2 he3
am7 4000 2000 active fm first yes 1 fm3
am8 5000 3000 active he second yes 2 he2 ;
proc print;
run;

proc sql;
create table want as select
   rep_cat,
   ifn(lien_position="first", sum(endbal),0) as Lien_1 format=Dollar30.2,
   ifn(lien_position="second", sum(endbal),0) as Lien_2 format=Dollar30.2,
   ifn(lien_position="first" or lien_position="second", sum(endbal),0) as Total format=Dollar30.2

   from have
   group by rep_cat    /* GROUP BY clause needed otherwise the summary function and HAVING clause treat table as one group */
   having lien_position="first"    /* HAVING clause is required below to summarize by participating lien_positions */
       or lien_position="second"   /* SAS is deterministic so the query requires remerge summary stats back with orginial data */
   ;
Quit;

data want_final;
   /* first reference (with obs=0) to existing table creats an empty table with required structure */
   update want (obs=0) /* second reference updates with values and BY ensures only one record per BY value is outputed */
           want;
   by rep_cat;
Run;

SAS Output

vs Desired Output (done in pivot table on "have" data using excel)

Re: proc sql having clause pivot table

LinusH — Thu, 21 Jul 2016 20:18:53 GMT

Don't use SQL to create the actual pivot. SQL is mainly for data manipulation / aggregation, not advanced report layout.
Take a look at TABULATE and REPORT procedures instead.

topic Re: proc sql having clause pivot table in SAS Procedures

proc sql having clause pivot table

Re: proc sql having clause pivot table