hi all,

i have a query which needs to be done using proc sql. here is the question:

"I have a data containing, 10 transactions in a supermarket, now i want to find the sum of the money spent in last three transactions. i have a variable showing the time of transaction".

in normal SAS we can do this by sorting in descending order and introducing the ID variable and can write a proc summary or proc means.

but how to do the same in proc sql? can we do it in only one query?

thanx in advance,

-Karthik

That's tough for proc sql, but you could try and make a variable equal to _n_ and then do something like this:

data your_data;
set your_data;
new_var = _n_;
run;

Proc sql;
create table blah as
select sum(sum_var) as total
from your_table
where new_var >= 7;
quit;

Is this what you have in mind for a single query?

SELECT SUM(Amount) AS SumOfLastThree FROM (SELECT TOP 3 Amount, DateTime FROM TheTable ORDER BY DateTime DESC);

thank you slide rule.

that is what exactly i want.

-Karthik

hi all,
i have used the code mentioned above but SAS is not recognizing "top3". is there any other way of solving this in proc sql. I can solve this in base SAS, but i want to do it in sql.

-Karthik

Sorry, that's true, SAS SQL does not support the TOP keyword. It uses a funny thing called OBS instead. You'll need to set obs=3, but I'm not quite sure where you would put that to get it in a single statement before calculating the sum.

If your data is static (with 10 obs) and you always want the last three you could also try something like this:

proc sql;
create table newTable as
select sum(transaction) as trans_sum
from your_table (firstobs=8)
;
quit;

you would just have to make sure the data is sorted before using the firstobs data set option.

I have tried with 'obs' also. but its giving the error "column not found". is there any other way of using obs?

-Karthik