Desktop productivity for business analysts and programmers

Counting - cumulative

Reply
Contributor
Posts: 41

Counting - cumulative

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.

Super User
Posts: 19,171

Re: Counting - cumulative

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?

Contributor
Posts: 41

Re: Counting - cumulative

I'm using SAS tables. I'm using point and click, however, I can include a proc (program).
Contributor
Posts: 41

Re: Counting - cumulative

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

Super User
Posts: 19,171

Re: Counting - cumulative

Sample data and output please?

Contributor
Posts: 41

Re: Counting - cumulative

Two input tables below as well as desired output.

Community Manager
Posts: 2,889

Re: Counting - cumulative

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.

Contributor
Posts: 41

Re: Counting - cumulative

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

Contributor
Posts: 41

Re: Counting - cumulative

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
Contributor
Posts: 41

Re: Counting - cumulative

Output example:

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

 

 

Super User
Posts: 9,875

Re: Counting - cumulative

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;
Ask a Question
Discussion stats
  • 10 replies
  • 532 views
  • 1 like
  • 4 in conversation