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)
... View more
