Help using Base SAS procedures

proc sql; union seems cumbersome

Accepted Solution Solved
Reply
Valued Guide
Posts: 864
Accepted Solution

proc sql; union seems cumbersome

The code below works fine, the first part groups by date,fico,product, the second part does not group on fico, as I want it to be on the portfolio level, hence the variable 'Portfolio' as fico format=$9.

 

Is there a way to do this in one step without the union?  I'd like to only have one 'from' statement.  Pulling from the dataset twice doubles the time it takes to build this and I have many of them. 

 


proc sql;
create table avg_fico as
select distinct intnx('month',OPEN_DT,0,'e') as OPEN_DT format=mmddyy10.,fico as fico,product,sum(cbscore_new) as cbscore_num,sum(n_cbscore_new) as cbscore_denom
from mdj.uloc_format
group by OPEN_DT,fico,product
union
select distinct intnx('month',OPEN_DT,0,'e') as OPEN_DT format=mmddyy10.,'Portfolio' as fico format=$9.,product,sum(cbscore_new) as cbscore_num,sum(n_cbscore_new) as cbscore_denom  
from mdj.uloc_format
group by OPEN_DT,product
order by OPEN_DT,fico,product;


Accepted Solutions
Solution
‎04-24-2018 03:54 PM
Esteemed Advisor
Posts: 5,541

Re: proc sql; union seems cumbersome

Posted in reply to Steelers_In_DC

You can't summarize on two levels at once with SQL, but you can speed things up by asking for union all instead of union.

PG

View solution in original post


All Replies
Super User
Posts: 23,776

Re: proc sql; union seems cumbersome

Posted in reply to Steelers_In_DC

PROC MEANS will do multiple levels of calculation at once - since you're using different GROUP BY I'm not sure there's an easy way in SQL to accomplish that. It will also respect variable formats so you can apply a format to the date to ensure the calculation is correct. 

Solution
‎04-24-2018 03:54 PM
Esteemed Advisor
Posts: 5,541

Re: proc sql; union seems cumbersome

Posted in reply to Steelers_In_DC

You can't summarize on two levels at once with SQL, but you can speed things up by asking for union all instead of union.

PG
Occasional Contributor
Posts: 17

Re: proc sql; union seems cumbersome

Not sure if this is a new topic or not but would you recommend this over doing an insert into, append or another data step with a set statement? I have seen most sql developers use the insert into method over the union but due to how SAS compiles am not sure which method is most advantageous.

Super User
Posts: 23,776

Re: proc sql; union seems cumbersome

Posted in reply to Larrihoover

Append is more advantageous because its not expecting any calculations and is a straight 'copy' type task. 

 

Other approaches allow you to modify the data while inserting/updating/union which can also have it's own advantages.

Super User
Super User
Posts: 8,127

Re: proc sql; union seems cumbersome

[ Edited ]
Posted in reply to Steelers_In_DC

Did you try using a PROC instead?

proc summary data=mdj.uloc_format ;
  class open_dt product fico ;
  types open_dt*product*fico open_dt*product ;
  format open_dt yymm7. ;
  var cbscore_new n_csscore_new ;
  output out=avg_fico sum=cbscore_num cbscore_denum ;
run;

If you have to roll you own then summarize at the most detailed level first and then build the higher level summaries from the summary instead of query the raw data again.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 111 views
  • 1 like
  • 5 in conversation