I have a caledar table where column INSESSION = 1 or 0.
I have another table that has a begin date and an end date.
Using a case statement, is there a way in SAS Enterprise Guide to total the amount in a range?
Example:
calendar table Jun 1 = 0
Jun 2 = 1
Jun 3 = 1
other table begin date = Jun 2
end date = Jun 3
Output should = 2
Thanks.
It depends, you need to provide a sample of your current data structure for both data sets.
Are you using the point and click interface or can you use code as well?
By student, how many days are insession (=1).
Sample data and output please?
Two input tables below as well as desired output.
As @Reeza says, sample data and output will help answer the question. But I think you can accomplish with the Query Builder. Drag your category (student? date?) over, then the INSESSION variable and apply the SUM aggregation to it. It will automatically group the calculation by your categories.
Are you able to look at the two input tables and desired output and advised?
CALENDAR TABLE | ||
DATE_VALUE | INSESSION | |
24Aug2012 0:00:00 | 0 | |
25Aug2012 0:00:00 | 0 | |
26Aug2012 0:00:00 | 0 | |
27Aug2012 0:00:00 | 1 | |
28Aug2012 0:00:00 | 1 | |
29Aug2012 0:00:00 | 1 | |
30Aug2012 0:00:00 | 1 | |
31Aug2012 0:00:00 | 1 | |
01Sep2012 0:00:00 | 0 | |
02Sep2012 0:00:00 | 0 | |
03Sep2012 0:00:00 | 0 | |
04Sep2012 0:00:00 | 1 | |
05Sep2012 0:00:00 | 1 | |
06Sep2012 0:00:00 | 1 | |
07Sep2012 0:00:00 | 1 | |
08Sep2012 0:00:00 | 0 | |
STUDENT TABLE | ||
Student # | Entry Date | Exit Date |
2343 | 08Apr2014 | 13Jun2014 |
5475 | 07May2014 | 13Jun2014 |
6789 | 25Feb2014 | 13Jun2014 |
6786 | 11Apr2014 | 13Jun2014 |
6034 | 22Jan2014 | 10Apr2014 |
3433 | 28Jan2014 | 13Jun2014 |
3457 | 10Dec2013 | 22Jan2014 |
3430 | 28Aug2013 | 11Sep2013 |
1235 | 05Feb2014 | 20Feb2014 |
2321 | 12May2014 | 13Jun2014 |
5694 | 20Mar2014 | 08Apr2014 |
4058 | 14May2014 | 02Jun2014 |
5053 | 05May2014 | 02Jun2014 |
5055 | 26Aug2013 | 13Jun2014 |
5101 | 06Nov2013 | 13Jun2014 |
5279 | 26Aug2013 | 24Oct2013 |
Output example:
Student # | # days |
1 | 2 |
2 | 6 |
3 | 10 |
4 | 4 |
5 | 6 |
|
data CALENDAR ;
infile cards expandtabs truncover;
input DATE_VALUE & anydtdtm. INSESSION;
DATE=datepart(DATE_VALUE);
format DATE_VALUE datetime. DATE date9.;
cards;
24Aug2012 0:00:00 0
25Aug2012 0:00:00 0
26Aug2012 0:00:00 0
27Aug2012 0:00:00 1
28Aug2012 0:00:00 1
29Aug2012 0:00:00 1
30Aug2012 0:00:00 1
31Aug2012 0:00:00 1
01Sep2012 0:00:00 0
02Sep2012 0:00:00 0
03Sep2012 0:00:00 0
04Sep2012 0:00:00 1
05Sep2012 0:00:00 1
06Sep2012 0:00:00 1
07Sep2012 0:00:00 1
08Sep2012 0:00:00 0
;
run;
data STUDENT ;
infile cards expandtabs truncover;
input Student EntryDate : date9. ExitDate : date9.;
format EntryDate ExitDate : date9.;
cards;
2343 08Apr2012 13Sep2012
5475 07May2014 13Jun2014
6789 25Feb2014 13Jun2014
6786 11Apr2014 13Jun2014
6034 22Jan2014 10Apr2014
3433 28Jan2014 13Jun2014
3457 10Dec2013 22Jan2014
3430 28Aug2013 11Sep2013
1235 05Feb2014 20Feb2014
2321 12May2014 13Jun2014
5694 20Mar2014 08Apr2014
4058 14May2014 02Jun2014
5053 05May2014 02Jun2014
5055 26Aug2013 13Jun2014
5101 06Nov2013 13Jun2014
5279 26Aug2013 24Oct2013
;
run;
proc sql;
create table want as
select a.*,(select sum(INSESSION) from CALENDAR where DATE between a.EntryDate and a.ExitDate) as days
from STUDENT as a;
quit;
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.