DATA Step, Macro, Functions and more

proc sql to create two datasets from one dataset

Reply
Frequent Contributor
Posts: 100

proc sql to create two datasets from one dataset

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 

PROC Star
Posts: 326

Re: proc sql to create two datasets from one dataset

[ Edited ]

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

Respected Advisor
Posts: 4,936

Re: proc sql to create two datasets from one dataset

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;
PG
Super User
Super User
Posts: 7,997

Re: proc sql to create two datasets from one dataset

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.

Frequent Contributor
Posts: 100

Re: proc sql to create two datasets from one dataset

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

Trusted Advisor
Posts: 1,934

Re: proc sql to create two datasets from one dataset

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;
Frequent Contributor
Posts: 100

Re: proc sql to create two datasets from one dataset

Posted in reply to PaigeMiller

Thanks for the replies. I appreciate your help.

Ask a Question
Discussion stats
  • 6 replies
  • 172 views
  • 1 like
  • 5 in conversation