topic Re: proc sql; union seems cumbersome in SAS Procedures

proc sql; union seems cumbersome

Steelers_In_DC — Thu, 22 Feb 2018 20:35:15 GMT

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;

Re: proc sql; union seems cumbersome

Reeza — Thu, 22 Feb 2018 20:38:19 GMT

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.

Re: proc sql; union seems cumbersome

PGStats — Thu, 22 Feb 2018 21:09:19 GMT

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.

Re: proc sql; union seems cumbersome

Larrihoover — Thu, 22 Feb 2018 21:38:07 GMT

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.

Re: proc sql; union seems cumbersome

Reeza — Thu, 22 Feb 2018 22:07:31 GMT

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.

Re: proc sql; union seems cumbersome

Tom — Fri, 23 Feb 2018 04:01:32 GMT

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.