Help using Base SAS procedures

Subsetting

Reply
Super Contributor
Posts: 1,041

Subsetting

Hi ,

I have the following

For each ID i want to pick record_date if its greater than or equal to First_date and lessthan or equla to 3 months from first _date

basically for each ID i want to pick record_dates which are within 3 months of first_date

HAVE

Id     first_date            record_date
101    01JAN2013     07FEB2013:12:15
101    01JAN2013     05MAR2013:13:07
101    01JAN2013     18APR2013:13:11
101    01JAN2013     23APR2013:14:28
101    01JAN2013     07MAY2013:12:45
101    01JAN2013     06JUN2013:12:11
101    01JAN2013     01JUL2013:11:38
101    01JAN2013     15AUG2013:12:56

102    22APR2013     15FEB2013:14:08
102    22APR2013     19FEB2013:12:51
102    22APR2013     25FEB2013:15:07
102    22APR2013     05MAR2013:13:20
102    22APR2013     07MAR2013:15:01
102    22APR2013     04JUN2013:14:11
102    22APR2013     19JUL2013:14:18
102    22APR2013     24JUL2013:14:43
102    22APR2013     02AUG2013:10:07
102    22APR2013     12AUG2013:14:21

WANT
Id     first_date    record_date
101    01JAN2013     07FEB2013:12:15
101    01JAN2013     05MAR2013:13:07

102    22APR2013     04JUN2013:14:11
102    22APR2013     19JUL2013:14:18

Thanks

Respected Advisor
Posts: 4,920

Re: Subsetting

Posted in reply to robertrao

Assuming first_date is a SAS date and record_date is a SAS datetime :


data want;

set have;

if intck("MONTH", first_date,  datepart(record_date), "CONTINUOUS") < 3;

run;

PG

Message was edited by: PG - Changed "SAME" with "CONTINUOUS"

PG
Super Contributor
Posts: 1,041

Re: Subsetting

Hi Thanks for the reply.

I was wondering what is the significance of "SAME" in this logic??

Thanks

Respected Advisor
Posts: 4,920

Re: Subsetting

Posted in reply to robertrao

Oups, sorry, The last argument should be "CONTINUOUS" and not "SAME". The default behaviour of function INTCK is to count the number of boundaries crossed (months in this case) So INTCK("MONTH", '31DEC1999'd, '15JAN2000'd) returns 1 because the first day of the month boundary was crossed once between the dates but INTCK("MONTH", '31DEC1999'd, '15JAN2000'd, "CONTINUOUS") returns 0 because there is less than a whole month between the two dates.

PG

PG
Trusted Advisor
Posts: 1,228

Re: Subsetting

Posted in reply to robertrao

My try

data have;

input Id  first_date :anydtdte. record_date datetime19.;

format first_date date9. record_date datetime19.;

datalines;

101    01JAN2013     07FEB2013:12:15

101    01JAN2013     05MAR2013:13:07

101    01JAN2013     18APR2013:13:11

101    01JAN2013     23APR2013:14:28

101    01JAN2013     07MAY2013:12:45

101    01JAN2013     06JUN2013:12:11

101    01JAN2013     01JUL2013:11:38

101    01JAN2013     15AUG2013:12:56

102    22APR2013     15FEB2013:14:08

102    22APR2013     19FEB2013:12:51

102    22APR2013     25FEB2013:15:07

102    22APR2013     05MAR2013:13:20

102    22APR2013     07MAR2013:15:01

102    22APR2013     04JUN2013:14:11

102    22APR2013     19JUL2013:14:18

102    22APR2013     24JUL2013:14:43

102    22APR2013     02AUG2013:10:07

102    22APR2013     12AUG2013:14:21

;;;;

proc sql;

select *

from have

where record_date>=first_date and  intck('days',first_date,datepart(record_date))>0 and intck('days',first_date,datepart(record_date))<= 90;

quit;

Super User
Posts: 10,023

Re: Subsetting

Posted in reply to robertrao

The problem here is your date and datetime variables .

data have;
input Id  first_date :anydtdte. record_date datetime19.;
format first_date date9. record_date datetime19.;
datalines4;
101    01JAN2013     07FEB2013:12:15
101    01JAN2013     05MAR2013:13:07
101    01JAN2013     18APR2013:13:11
101    01JAN2013     23APR2013:14:28
101    01JAN2013     07MAY2013:12:45
101    01JAN2013     06JUN2013:12:11
101    01JAN2013     01JUL2013:11:38
101    01JAN2013     15AUG2013:12:56
102    22APR2013     15FEB2013:14:08
102    22APR2013     19FEB2013:12:51
102    22APR2013     25FEB2013:15:07
102    22APR2013     05MAR2013:13:20
102    22APR2013     07MAR2013:15:01
102    22APR2013     04JUN2013:14:11
102    22APR2013     19JUL2013:14:18
102    22APR2013     24JUL2013:14:43
102    22APR2013     02AUG2013:10:07
102    22APR2013     12AUG2013:14:21
;;;;
run;
data want;
 set have;
 if first_date le datepart(record_date) lt intnx('month',first_date,3,'s');
run;

Xia Keshan

Ask a Question
Discussion stats
  • 5 replies
  • 283 views
  • 0 likes
  • 4 in conversation