AUTOMATION FOR SUM FUNCTION

[KatiaBravo]

Hey there community!! 😄

I wanted to know if it is possible to automate a SUM function to iterate, for example:

I have this:

PROC SQL;

CREATE TABLE combined3 AS SELECT PAYROLL, NAME, SUM(_V01JUN2021) AS _V01JUN2021, SUM(_V02JUN2021) AS _V02JUN2021, SUM(_V03JUN2021) AS _V03JUN2021, SUM(_V04JUN2021) AS _V04JUN2021, SUM(_V05JUN2021) AS _V05JUN2021, (...) ,SUM(_V30JUN2021) AS _V30JUN2021

But every month the headers _V01JUN2021 will change for _V01JUL2021, so I would have to change every month all the code above, is there any way to automate this?

Thank you all in advanced! 

[Reeza · Accepted solution]

If you use PROC MEANS it does most of this automatically.
Do you have to use SQL?

proc means data=have noprint nway;
class payroll name;
var _V: ;
output out=want sum= ;
run;

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

---

@KatiaBravo wrote:

Hey there community!! 😄

I wanted to know if it is possible to automate a SUM function to iterate, for example:

I have this:

PROC SQL;

CREATE TABLE combined3 AS SELECT PAYROLL, NAME, SUM(_V01JUN2021) AS _V01JUN2021, SUM(_V02JUN2021) AS _V02JUN2021, SUM(_V03JUN2021) AS _V03JUN2021, SUM(_V04JUN2021) AS _V04JUN2021, SUM(_V05JUN2021) AS _V05JUN2021, (...) ,SUM(_V30JUN2021) AS _V30JUN2021

But every month the headers _V01JUN2021 will change for _V01JUL2021, so I would have to change every month all the code above, is there any way to automate this?

Thank you all in advanced! 

---

[KatiaBravo]

Thanks a ton!!! 

I was trying to do SQL because I thought I needed to do a loop or a DO, but this worked just fine!!!!

Thanks for real!! 😄

[ChrisNZ]

Do the sum by day before you transpose the table.

[KatiaBravo]

Thaaaanks again Chris!!! I always get so confused for real!! 😞
Will take all of your advice! 😄

[andreas_lds]

I would like to emphasis what @ChrisNZ already recommended:

Do the sum by day before you transpose the table.

It would skip transposing the data at all, because as soon as you have data in variable-names, all subsequent steps will become more difficult to write. In the worst-case you end up using tons of macro-variables making the code as readable as cuneiform script.

[KatiaBravo]

Thanks for your advice, will keep this in mind to enhance my code! 😄