proc sql having clause pivot table

[blakezen]

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)

[LinusH]

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.