BookmarkSubscribeRSS Feed
Obsidian | Level 7

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!




data have;
input namel $ endbal netof accst $ balsheet$ lien_position $ impairec $ lien_pp rep_cat $;
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;

proc sql;
create table want as select
    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 */    

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 */
    by rep_cat;


SAS OutputCapture.PNG


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


Tourmaline | Level 20
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.
Data never sleeps



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation