topic Re: proc sql to create two datasets from one dataset in SAS Programming

proc sql to create two datasets from one dataset

buddha_d — Wed, 15 Nov 2017 04:13:50 GMT

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

Re: proc sql to create two datasets from one dataset

kiranv_ — Wed, 15 Nov 2017 04:48:12 GMT

One proc sql cannot create 2 datasets. You need to use a datastep with output to different datasets.

Re: proc sql to create two datasets from one dataset

PGStats — Wed, 15 Nov 2017 05:38:37 GMT

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;

Re: proc sql to create two datasets from one dataset

RW9 — Wed, 15 Nov 2017 10:26:05 GMT

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.

Re: proc sql to create two datasets from one dataset

buddha_d — Wed, 15 Nov 2017 13:14:54 GMT

Sorry for late response. I would try recommended code. Thanks all for helping me out here.

Re: proc sql to create two datasets from one dataset

PaigeMiller — Wed, 15 Nov 2017 13:35:31 GMT

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;

Re: proc sql to create two datasets from one dataset

buddha_d — Thu, 16 Nov 2017 04:21:37 GMT

Thanks for the replies. I appreciate your help.