11-14-2017 11:13 PM
I am trying to create two datasets from one huge dataset. One I need to create last two weeks data and sum it and second data is to sum it for the entire month. I need to see the difference for the two weeks.
I am using following code to create two datasets. I was wondering if I can create one proc sql to create two datasets.
create table two_weeks as
select a.*, sum (rec_amount) as two_sum
from walgreen.history a
where date >= today()-15; quit;
create table month as
select a.*, sum (rec_amount) as month_sum, (calculated month_sum-b.two_sum ) as balance
from walgreen.history a, two_weeks b
where date >= today()-30; quit;
since i am using the same dataset to create two datasets as two different datasets, Is there a way to create a single proc sql code to accomplish the task I am trying to do?
thanks in advance
11-14-2017 11:45 PM - edited 11-14-2017 11:48 PM
One proc sql cannot create 2 datasets. You need to use a datastep with output to different datasets.
11-15-2017 12:38 AM
Something like this perhaps:
proc sql; create table want as select *, sum(amount) as month_sum, calculated month_sum - two_sum as balance from ( select *, intck('day15', date, today(), "CONTINUOUS") as d15Period, sum(amount) as two_sum from have where calculated d15Period <2 group by calculated d15Period ) ; select * from want; quit;
11-15-2017 05:26 AM
Yes, there is a way, use a datastep - with that you can process the data in one datastep and output two datasets. SQL only has to be used when being passed through to a database, otherwise you can use datastep or SQL for any problem.
11-15-2017 08:35 AM
Two datasets are not needed.
Create a new column where rec_amount is missing if the data is not in the last 2 weeks; and create another new column where rec_amount is missing if the data is not from the last month.
Then, PROC SUMMARY can compute the sums or means for the desired time periods.
proc summary data=have; var last2weeks lastmonth; output out=stats sum=; run;