topic Re: AUTOMATION FOR SUM FUNCTION in SAS Programming

AUTOMATION FOR SUM FUNCTION

KatiaBravo — Mon, 05 Jul 2021 22:52:19 GMT

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!

Re: AUTOMATION FOR SUM FUNCTION

Reeza — Mon, 05 Jul 2021 23:02:31 GMT

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!

Re: AUTOMATION FOR SUM FUNCTION

ChrisNZ — Tue, 06 Jul 2021 04:35:52 GMT

Do the sum by day before you transpose the table.

Re: AUTOMATION FOR SUM FUNCTION

andreas_lds — Tue, 06 Jul 2021 05:32:33 GMT

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.

Re: AUTOMATION FOR SUM FUNCTION

KatiaBravo — Tue, 06 Jul 2021 17:24:48 GMT

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!! 😄

Re: AUTOMATION FOR SUM FUNCTION

KatiaBravo — Tue, 06 Jul 2021 17:26:48 GMT

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

Re: AUTOMATION FOR SUM FUNCTION

KatiaBravo — Tue, 06 Jul 2021 17:27:44 GMT

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