Help using Base SAS procedures

Queries that have to do with dates

Reply
Frequent Contributor
Posts: 99

Queries that have to do with dates

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.

ChildIDACTACTDateAgencySpellBeginAgencySpellEnd
Childid1M99911/30/119/12/0112/12/2414
Childid1M95611/12/119/12/0112/12/2414
Childid2M9907/12/117/12/022/12/2414
Childid3M9991/1/126/13/042/12/2414
Childid3M90011/11/11 6/13/042/12/2414
Childid3M95610/12/116/13/042/12/2414
Childid4M9561/1/118/9/041/12/12
Childid5M4509/12/117/12/052/12/2414
Childid6M9568/12/117/12/031/12/11
Childid7M9561/12/121/12/092/12/2414

The birthdays are located in another table

ChildIDDOB
Childid11/20/1990
Childid22/30/1995
Childid38/30/1992
Childid45/30/1999
Childid56/08/2001
Childid64/16/2002
Childid76/7/1997

Thanks for your help!

Contributor
Posts: 28

Re: Queries that have to do with dates

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

Respected Advisor
Posts: 3,124

Re: Queries that have to do with dates

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

Ask a Question
Discussion stats
  • 2 replies
  • 136 views
  • 0 likes
  • 3 in conversation