topic Re: Subsetting in SAS Procedures

Subsetting

robertrao — Thu, 01 May 2014 19:45:02 GMT

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

Re: Subsetting

PGStats — Thu, 01 May 2014 20:22:50 GMT

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;

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

Re: Subsetting

stat_sas — Thu, 01 May 2014 20:42:23 GMT

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;

Re: Subsetting

robertrao — Thu, 01 May 2014 20:58:03 GMT

Hi Thanks for the reply.

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

Thanks

Re: Subsetting

PGStats — Thu, 01 May 2014 21:42:38 GMT

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.

Re: Subsetting

Ksharp — Fri, 02 May 2014 02:33:25 GMT

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