BookmarkSubscribeRSS Feed
buddha_d
Pyrite | Level 9

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 

6 REPLIES 6
kiranv_
Rhodochrosite | Level 12

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

PGStats
Opal | Level 21

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

buddha_d
Pyrite | Level 9

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

PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
buddha_d
Pyrite | Level 9

Thanks for the replies. I appreciate your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 5210 views
  • 1 like
  • 5 in conversation