BookmarkSubscribeRSS Feed
blakezen
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!

 

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

 

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

Capture.PNG

1 REPLY 1
LinusH
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 1 reply
  • 3561 views
  • 0 likes
  • 2 in conversation