Queries that have to do with dates

[RobertNYC]

Hello--

I am very new to SAS programing. I have to perform a few basic queries. Any assistance will be greatly appreciated .

So, i work in child welfare:

1. I have to determine the amount of AWOLs that took place between today() and 7 months ago.

2. I have to identify all the kids in our system that are greater than 14.5 years of age as of today().

3. I have to identivy all the kids in our system that are greater than 19.5. of age as of today().

Below is a sample table.

The "ACT" Colum is where all the movement codes are entered. M956 stands for AWOL and M999 means they returned.

The "ACTDate Colum is when the movment took place

"AgencySpellBegin" Colum is the date the child entered care at an agency

"AgencySpellEnd" Colum is the date the child left care, if the date  is in the far future than that means the child is still in care.

I need to know also if the child did AWOL (M956) that child returned (M999) and the date of the return.

ChildID	ACT	ACTDate	AgencySpellBegin	AgencySpellEnd
Childid1	M999	11/30/11	9/12/01	12/12/2414
Childid1	M956	11/12/11	9/12/01	12/12/2414
Childid2	M990	7/12/11	7/12/02	2/12/2414
Childid3	M999	1/1/12	6/13/04	2/12/2414
Childid3	M900	11/11/11	6/13/04	2/12/2414
Childid3	M956	10/12/11	6/13/04	2/12/2414
Childid4	M956	1/1/11	8/9/04	1/12/12
Childid5	M450	9/12/11	7/12/05	2/12/2414
Childid6	M956	8/12/11	7/12/03	1/12/11
Childid7	M956	1/12/12	1/12/09	2/12/2414

The birthdays are located in another table

ChildID	DOB
Childid1	1/20/1990
Childid2	2/30/1995
Childid3	8/30/1992
Childid4	5/30/1999
Childid5	6/08/2001
Childid6	4/16/2002
Childid7	6/7/1997

Thanks for your help!

[mojerry2]

point 1 is just counting the number of act='M956' and agencyspellbegin ge "7 months ago" and agencyspellbegin le today()

how do you calculate 7 months ago? with the magic intnx function. intnx('month',mdy(month(today()),1,year(today()),-7)

let's explain, intnx increments the given date with the interval given and the number of intervals. interval = month and number = -7.

point 2 is calculating the age. So you ask sas the number of months between date of birth and today devided by 12.

          c=intck('month',dob,today())/12;

          c gives you the age in years but with a detail after the comma. with this calculated column you can then identify the age. if you want to count, same story as point 1.

the hidden question about knowing if the shild is returned is using the first and last pointer in a datastep.

added detail

[Haikuo]

For Q1, SQL plus intnx() mentioned by mojerry will make one of the solutions.

data have;

infile cards;

input ChildID $10.     ACT $     ACTDate :mmddyy8.     AgencySpellBegin : mmddyy10.

       AgencySpellEnd : mmddyy10.;

format ACTDate     date9.

       AgencySpellBegin date9.

       AgencySpellEnd date9.;

cards;

Childid1     M999     11/30/11     9/12/01     12/12/2414

Childid1     M956     11/12/11     9/12/01     12/12/2414

Childid2     M990     7/12/11     7/12/02     2/12/2414

Childid3     M999     1/1/12     6/13/04     2/12/2414

Childid3     M900     11/11/11      6/13/04     2/12/2414

Childid3     M956     10/12/11     6/13/04     2/12/2414

Childid4     M956     1/1/11     8/9/04     1/12/12

Childid5     M450     9/12/11     7/12/05     2/12/2414

Childid6     M956     8/12/11     7/12/03     1/12/11

Childid7     M956     1/12/12     1/12/09     2/12/2414

;

/*Q1 , awols and returns*/

proc sql;

create table awol as

select distinct childid from have

where  act='M956' and actdate>=intnx('month',today(),-7,'sameday')

;

create table return as

select distinct childid from have

where actdate>=intnx('month',today(),-7,'sameday')

group by childid

having sum(act='M956')>0 and sum(act='M999')>0

;

quit;

Please note, if you want to define your month from the first day of every month, you need to remove 'sameday' option from intnx().

Q2 seems to be straighforward question, however, results could vary depend on what you want. Please refer a recent discussion on SAS-L.

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1201B&L=sas-l&P=R13704

Regards,

Haikuo