topic Queries that have to do with dates in SAS Procedures

Queries that have to do with dates

RobertNYC — Fri, 03 Feb 2012 05:40:45 GMT

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!

Re: Queries that have to do with dates

mojerry2 — Fri, 03 Feb 2012 09:08:27 GMT

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

Re: Queries that have to do with dates

Haikuo — Fri, 03 Feb 2012 13:33:05 GMT

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