Re: Loop sum statement in PROC SQL

sivakoya · Posted 10-17-2019 03:34 PM

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

Kurt_Bremser · Posted 10-17-2019 03:48 PM

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

See Maxim 19.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Reeza · Posted 10-17-2019 04:13 PM

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

SASKiwi · Posted 10-17-2019 05:57 PM

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.

Loop sum statement in PROC SQL