guys,
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.
proc sql;
create table two_weeks as
select a.*, sum (rec_amount) as two_sum
from walgreen.history a
where date >= today()-15; quit;
proc sql;
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
One proc sql cannot create 2 datasets. You need to use a datastep with output to different datasets.
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;
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.
Sorry for late response. I would try recommended code. Thanks all for helping me out here.
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.
For example:
proc summary data=have;
var last2weeks lastmonth;
output out=stats sum=;
run;
Thanks for the replies. I appreciate your help.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.