topic Re: Counting - cumulative in SAS Enterprise Guide

Counting - cumulative

carmendee — Mon, 29 Feb 2016 17:13:53 GMT

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.

Re: Counting - cumulative

Reeza — Mon, 29 Feb 2016 17:15:11 GMT

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?

Re: Counting - cumulative

carmendee — Mon, 29 Feb 2016 17:39:32 GMT

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

Re: Counting - cumulative

carmendee — Mon, 29 Feb 2016 18:20:11 GMT

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

Re: Counting - cumulative

Reeza — Mon, 29 Feb 2016 18:27:00 GMT

Sample data and output please?

Re: Counting - cumulative

ChrisHemedinger — Mon, 29 Feb 2016 18:30:06 GMT

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.

Re: Counting - cumulative

carmendee — Mon, 29 Feb 2016 18:53:52 GMT

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

Re: Counting - cumulative

carmendee — Mon, 29 Feb 2016 19:05:28 GMT

Output example:

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

Re: Counting - cumulative

carmendee — Mon, 29 Feb 2016 19:35:25 GMT

Two input tables below as well as desired output.

Re: Counting - cumulative

carmendee — Mon, 29 Feb 2016 21:22:31 GMT

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

Re: Counting - cumulative

Ksharp — Tue, 01 Mar 2016 01:31:09 GMT

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;