BookmarkSubscribeRSS Feed
carmendee
Obsidian | Level 7

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.

10 REPLIES 10
Reeza
Super User

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?

carmendee
Obsidian | Level 7
I'm using SAS tables. I'm using point and click, however, I can include a proc (program).
carmendee
Obsidian | Level 7

By student, how many days are insession (=1).

Reeza
Super User

Sample data and output please?

carmendee
Obsidian | Level 7

Two input tables below as well as desired output.

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
carmendee
Obsidian | Level 7

Are you able to look at the two input tables and desired output and advised? 

carmendee
Obsidian | Level 7
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
carmendee
Obsidian | Level 7

Output example:

Student # # days
1 2
2 6
3 10
4 4
5 6
 

 

 

Ksharp
Super User
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;

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 10 replies
  • 1683 views
  • 1 like
  • 4 in conversation