topic Re: Loop sum statement in PROC SQL in New SAS User

Loop sum statement in PROC SQL

sivakoya — Thu, 17 Oct 2019 19:34:51 GMT

proc sql;
create table Vintage_Summary_final_payoff as
select
*,
sum('201301_sur'n) as '201301_sur_Payoff'n,
sum('201302_sur'n) as '201302_sur_Payoff'n,
sum('201303_sur'n) as '201303_sur_Payoff'n,
sum('201304_sur'n) as '201304_sur_Payoff'n,
sum('201305_sur'n) as '201305_sur_Payoff'n,
sum('201306_sur'n) as '201306_sur_Payoff'n,
sum('201307_sur'n) as '201307_sur_Payoff'n,
sum('201308_sur'n) as '201308_sur_Payoff'n,
sum('201309_sur'n) as '201309_sur_Payoff'n,
sum('201310_sur'n) as '201310_sur_Payoff'n,
sum('201311_sur'n) as '201311_sur_Payoff'n,
sum('201312_sur'n) as '201312_sur_Payoff'n,
sum('201401_sur'n) as '201401_sur_Payoff'n,
sum('201402_sur'n) as '201402_sur_Payoff'n,
sum('201403_sur'n) as '201403_sur_Payoff'n,
sum('201404_sur'n) as '201404_sur_Payoff'n,
sum('201405_sur'n) as '201405_sur_Payoff'n,
sum('201406_sur'n) as '201406_sur_Payoff'n,
sum('201407_sur'n) as '201407_sur_Payoff'n,
sum('201408_sur'n) as '201408_sur_Payoff'n,
sum('201409_sur'n) as '201409_sur_Payoff'n,
sum('201410_sur'n) as '201410_sur_Payoff'n,
sum('201411_sur'n) as '201411_sur_Payoff'n,
sum('201412_sur'n) as '201412_sur_Payoff'n,
sum('201501_sur'n) as '201501_sur_Payoff'n,
sum('201502_sur'n) as '201502_sur_Payoff'n,
sum('201503_sur'n) as '201503_sur_Payoff'n,
sum('201504_sur'n) as '201504_sur_Payoff'n,
sum('201505_sur'n) as '201505_sur_Payoff'n,
sum('201506_sur'n) as '201506_sur_Payoff'n,
sum('201507_sur'n) as '201507_sur_Payoff'n,
sum('201508_sur'n) as '201508_sur_Payoff'n,
sum('201509_sur'n) as '201509_sur_Payoff'n,
sum('201510_sur'n) as '201510_sur_Payoff'n,
sum('201511_sur'n) as '201511_sur_Payoff'n,
sum('201512_sur'n) as '201512_sur_Payoff'n,
sum('201601_sur'n) as '201601_sur_Payoff'n,
sum('201602_sur'n) as '201602_sur_Payoff'n,
sum('201603_sur'n) as '201603_sur_Payoff'n,
sum('201604_sur'n) as '201604_sur_Payoff'n,
sum('201605_sur'n) as '201605_sur_Payoff'n,
sum('201606_sur'n) as '201606_sur_Payoff'n,
sum('201607_sur'n) as '201607_sur_Payoff'n,
sum('201608_sur'n) as '201608_sur_Payoff'n,
sum('201609_sur'n) as '201609_sur_Payoff'n,
sum('201610_sur'n) as '201610_sur_Payoff'n,
sum('201611_sur'n) as '201611_sur_Payoff'n,
sum('201612_sur'n) as '201612_sur_Payoff'n,
sum('201701_sur'n) as '201701_sur_Payoff'n,
sum('201702_sur'n) as '201702_sur_Payoff'n,
sum('201703_sur'n) as '201703_sur_Payoff'n,
sum('201704_sur'n) as '201704_sur_Payoff'n,
sum('201705_sur'n) as '201705_sur_Payoff'n,
sum('201706_sur'n) as '201706_sur_Payoff'n,
sum('201707_sur'n) as '201707_sur_Payoff'n,
sum('201708_sur'n) as '201708_sur_Payoff'n,
sum('201709_sur'n) as '201709_sur_Payoff'n,
sum('201710_sur'n) as '201710_sur_Payoff'n,
sum('201711_sur'n) as '201711_sur_Payoff'n,
sum('201712_sur'n) as '201712_sur_Payoff'n,
sum('201801_sur'n) as '201801_sur_Payoff'n,
sum('201802_sur'n) as '201802_sur_Payoff'n,
sum('201803_sur'n) as '201803_sur_Payoff'n,
sum('201804_sur'n) as '201804_sur_Payoff'n,
sum('201805_sur'n) as '201805_sur_Payoff'n,
sum('201806_sur'n) as '201806_sur_Payoff'n,
sum('201807_sur'n) as '201807_sur_Payoff'n,
sum('201808_sur'n) as '201808_sur_Payoff'n,
sum('201809_sur'n) as '201809_sur_Payoff'n,
sum('201810_sur'n) as '201810_sur_Payoff'n,
sum('201811_sur'n) as '201811_sur_Payoff'n,
sum('201812_sur'n) as '201812_sur_Payoff'n,
sum('201901_sur'n) as '201901_sur_Payoff'n,
sum('201902_sur'n) as '201902_sur_Payoff'n,
sum('201903_sur'n) as '201903_sur_Payoff'n,
sum('201904_sur'n) as '201904_sur_Payoff'n,
sum('201905_sur'n) as '201905_sur_Payoff'n,
sum('201906_sur'n) as '201906_sur_Payoff'n,
sum('201907_sur'n) as '201907_sur_Payoff'n,
sum('201908_sur'n) as '201908_sur_Payoff'n,
sum('201909_sur'n) as '201909_sur_Payoff'n

from Vintage_Summary_final;
quit;

Need help automating above block of PROC sql if there is a way to loop through variables that end with '_sur' in Vintage_Summary_final data set

Re: Loop sum statement in PROC SQL

Kurt_Bremser — Thu, 17 Oct 2019 19:48:32 GMT

With a reasonable (long) dataset structure this would be a very simple sum with group by.

See Maxim 19.

Re: Loop sum statement in PROC SQL

Reeza — Thu, 17 Oct 2019 20:13:57 GMT

Why SQL? Why not use PROC MEANS instead? Using variable lists is much more efficient.

Also, naming variables that start with a number is a bad idea.

ods select none;
proc means data=vintage_summary_final sum;
var '201301_sur'n -- '201909_sur'n;
ods output summary=want;
run;
ods select all;

@sivakoya wrote:

proc sql;
create table Vintage_Summary_final_payoff as
select
*,
sum('201301_sur'n) as '201301_sur_Payoff'n,
sum('201302_sur'n) as '201302_sur_Payoff'n,
sum('201303_sur'n) as '201303_sur_Payoff'n,
sum('201304_sur'n) as '201304_sur_Payoff'n,
sum('201305_sur'n) as '201305_sur_Payoff'n,
sum('201306_sur'n) as '201306_sur_Payoff'n,
sum('201307_sur'n) as '201307_sur_Payoff'n,
sum('201308_sur'n) as '201308_sur_Payoff'n,
sum('201309_sur'n) as '201309_sur_Payoff'n,
sum('201310_sur'n) as '201310_sur_Payoff'n,
sum('201311_sur'n) as '201311_sur_Payoff'n,
sum('201312_sur'n) as '201312_sur_Payoff'n,
sum('201401_sur'n) as '201401_sur_Payoff'n,
sum('201402_sur'n) as '201402_sur_Payoff'n,
sum('201403_sur'n) as '201403_sur_Payoff'n,
sum('201404_sur'n) as '201404_sur_Payoff'n,
sum('201405_sur'n) as '201405_sur_Payoff'n,
sum('201406_sur'n) as '201406_sur_Payoff'n,
sum('201407_sur'n) as '201407_sur_Payoff'n,
sum('201408_sur'n) as '201408_sur_Payoff'n,
sum('201409_sur'n) as '201409_sur_Payoff'n,
sum('201410_sur'n) as '201410_sur_Payoff'n,
sum('201411_sur'n) as '201411_sur_Payoff'n,
sum('201412_sur'n) as '201412_sur_Payoff'n,
sum('201501_sur'n) as '201501_sur_Payoff'n,
sum('201502_sur'n) as '201502_sur_Payoff'n,
sum('201503_sur'n) as '201503_sur_Payoff'n,
sum('201504_sur'n) as '201504_sur_Payoff'n,
sum('201505_sur'n) as '201505_sur_Payoff'n,
sum('201506_sur'n) as '201506_sur_Payoff'n,
sum('201507_sur'n) as '201507_sur_Payoff'n,
sum('201508_sur'n) as '201508_sur_Payoff'n,
sum('201509_sur'n) as '201509_sur_Payoff'n,
sum('201510_sur'n) as '201510_sur_Payoff'n,
sum('201511_sur'n) as '201511_sur_Payoff'n,
sum('201512_sur'n) as '201512_sur_Payoff'n,
sum('201601_sur'n) as '201601_sur_Payoff'n,
sum('201602_sur'n) as '201602_sur_Payoff'n,
sum('201603_sur'n) as '201603_sur_Payoff'n,
sum('201604_sur'n) as '201604_sur_Payoff'n,
sum('201605_sur'n) as '201605_sur_Payoff'n,
sum('201606_sur'n) as '201606_sur_Payoff'n,
sum('201607_sur'n) as '201607_sur_Payoff'n,
sum('201608_sur'n) as '201608_sur_Payoff'n,
sum('201609_sur'n) as '201609_sur_Payoff'n,
sum('201610_sur'n) as '201610_sur_Payoff'n,
sum('201611_sur'n) as '201611_sur_Payoff'n,
sum('201612_sur'n) as '201612_sur_Payoff'n,
sum('201701_sur'n) as '201701_sur_Payoff'n,
sum('201702_sur'n) as '201702_sur_Payoff'n,
sum('201703_sur'n) as '201703_sur_Payoff'n,
sum('201704_sur'n) as '201704_sur_Payoff'n,
sum('201705_sur'n) as '201705_sur_Payoff'n,
sum('201706_sur'n) as '201706_sur_Payoff'n,
sum('201707_sur'n) as '201707_sur_Payoff'n,
sum('201708_sur'n) as '201708_sur_Payoff'n,
sum('201709_sur'n) as '201709_sur_Payoff'n,
sum('201710_sur'n) as '201710_sur_Payoff'n,
sum('201711_sur'n) as '201711_sur_Payoff'n,
sum('201712_sur'n) as '201712_sur_Payoff'n,
sum('201801_sur'n) as '201801_sur_Payoff'n,
sum('201802_sur'n) as '201802_sur_Payoff'n,
sum('201803_sur'n) as '201803_sur_Payoff'n,
sum('201804_sur'n) as '201804_sur_Payoff'n,
sum('201805_sur'n) as '201805_sur_Payoff'n,
sum('201806_sur'n) as '201806_sur_Payoff'n,
sum('201807_sur'n) as '201807_sur_Payoff'n,
sum('201808_sur'n) as '201808_sur_Payoff'n,
sum('201809_sur'n) as '201809_sur_Payoff'n,
sum('201810_sur'n) as '201810_sur_Payoff'n,
sum('201811_sur'n) as '201811_sur_Payoff'n,
sum('201812_sur'n) as '201812_sur_Payoff'n,
sum('201901_sur'n) as '201901_sur_Payoff'n,
sum('201902_sur'n) as '201902_sur_Payoff'n,
sum('201903_sur'n) as '201903_sur_Payoff'n,
sum('201904_sur'n) as '201904_sur_Payoff'n,
sum('201905_sur'n) as '201905_sur_Payoff'n,
sum('201906_sur'n) as '201906_sur_Payoff'n,
sum('201907_sur'n) as '201907_sur_Payoff'n,
sum('201908_sur'n) as '201908_sur_Payoff'n,
sum('201909_sur'n) as '201909_sur_Payoff'n

from Vintage_Summary_final;
quit;

Need help automating above block of PROC sql if there is a way to loop through variables that end with '_sur' in Vintage_Summary_final data set

Re: Loop sum statement in PROC SQL

SASKiwi — Thu, 17 Oct 2019 21:57:57 GMT

Why the non-SAS standard variable names? If they not essential why use them as you have to wrap quotes and an "N" around every one of them. So much extra typing.