topic Re: PROC SQL code to calculate accumalte data in SAS Programming

PROC SQL code to calculate accumalte data

Ronein — Tue, 22 Jun 2021 20:16:09 GMT

Hello,

May anyone explain the PROC SQL code to calculate accumalte data.

May expplain the order of execuations here

Re: PROC SQL code to calculate accumalte data

Reeza — Tue, 22 Jun 2021 20:22:09 GMT

Google CROSS JOIN.

Re: PROC SQL code to calculate accumalte data

tarheel13 — Tue, 22 Jun 2021 21:57:39 GMT

I don't really like SQL for a running total. I think it is better to use a data step or proc summary. Did you get this from a SAS paper?

Re: PROC SQL code to calculate accumalte data

Ksharp — Wed, 23 Jun 2021 12:29:18 GMT

Assuming you have data like :

obs value
1 1
2 2
3 3

So SQL could get this:
obs value cumulative
1 1 1 <-- due to this.obs<=b.obs(=1)
2 2 1+2 <-- due to this.obs<=b.obs(=2)
3 3 1+2+3 <-- due to this.obs<=b.obs(=3)

Re: PROC SQL code to calculate accumalte data

Ronein — Thu, 24 Jun 2021 14:16:15 GMT

My question was different:

I wanted to understand how SAS process this sql query.

For example:

SAS read data set "Shoes" and call it "b"

Then SAS read data set "Shoes" again and call it "a"

Then for each row SAS accumulate total of sales from first observation till current observation (included) .(Techincally it is a.obs<=b.obs)

Then SAS select the columns region ,product ,sales from b and select total sales

Is it true??

Re: PROC SQL code to calculate accumalte data

Ksharp — Sat, 26 Jun 2021 10:00:36 GMT

This is table B
obs value
1 1
2 2
3 3

For the first obs of B
1 1 <-- sql select the obs from table A where a.obs<=b.obs(=1) ,so only the first obs selected ,and sum(1)

For the second obs of B
2 2 <-- sql select the obs from table A where a.obs<=b.obs(=2) ,so only the first TWO obs selected ,and sum(1,2)

For the third obs of B
3 3 <-- sql select the obs from table A where a.obs<=b.obs(=3) ,so only the first THREE obs selected ,and sum(1,2,3)

........and so on .

Re: PROC SQL code to calculate accumalte data

Ronein — Fri, 25 Jun 2021 21:40:36 GMT

Perfect but just need to change to <= instaed of <

Re: PROC SQL code to calculate accumalte data

Ronein — Fri, 25 Jun 2021 21:41:47 GMT

So as I understand the answer is :

Let's think that this is table B and A

obs value
1 1
2 2
3 3

Go to first obs in table B

then select the obs from table A where a.obs<=b.obs(=1)

so only the first obs selected ,and sum(1)

Go to 2nd obs in table B

then select the obs from table A where a.obs<=b.obs(=2)

so only the first TWO obs selected ,and sum(1,2)

Go to 3^rd obs in table B

then select the obs from table A where a.obs<=b.obs(=3)

so only the first THREE obs selected ,and sum(1,2,3)

........and so on