PROC SQL code to calculate accumalte data

[Ronein]

Hello,

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

May expplain the order of execuations here

[Reeza]

Google CROSS JOIN.

[tarheel13]

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?

[Ksharp]

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)

[Ronein]

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??

[Ksharp · Accepted solution]

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 .

[Ronein]

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

[Ronein]

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