BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Steelers_In_DC
Barite | Level 11

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
Reeza
Super User

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. 

PGStats
Opal | Level 21

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
Larrihoover
Obsidian | Level 7

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.

Reeza
Super User

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.

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 5 replies
  • 1315 views
  • 1 like
  • 5 in conversation