code help

[user1]

Hi all,

I’d like to ask if you can help on this. I would like to know the numbers of visits one year before the date of ‘time_threshold’in table one, but not including the visit on the date of time_threshold. For example, the first person’s time_threshold is 20050917, then the number of visits 12 months before this date is 2 times (not including the 'time_threshold' visit). Table two includes all records in terms of visit time by each person. 

The table that I am looking for is as follows:

Id	Counts
1	2
2	1
3	0
4	1

data one;

input id time_threshold yymmdd10.;

format time_threshold yymmdd10.;

datalines;

1 20050917

2 20071005

3 20050606

4 20090116

;

run;

data two;

input id visit_time yymmdd10.;

format visit_time yymmdd10.;

datalines;

1 20041212

1 20031105

1 20050917

1 20050407

1 20050112

2 20071005

2 20090301

2 20061215

2 20110915

3 20050606

3 20050918

4 20101112

4 20090116

4 20080909

;

run;

Thanks  a lot for your help!

[Reeza]

proc sql;

create table summary as

select one.id, count(*) as num_visits

from one

left join two

on one.id=two.id

and one.time_threshold-two.visit_time between 1 and 365

group by one.id;

quit;

[user1]

Thanks very much for your prompt reply! I run the code, but the results for person 1 and 3 did not look correct. For example, person 3 should have 0 visit, but the result showed 1 visit for this person.

[Reeza · Accepted solution]

it should be count(visit_time) rather than count(*) actually....

And 1 looks, correct, three visits:

2004-12-12

2005-01-12

2005-04-07

See the full details before the filter/sort.

proc sql;

create table summary as

select one.id, count(visit_time) as num_visits, *, time_threshold-visit_time as diff

from one

left join two

on one.id=two.id;

/*and one.time_threshold-two.visit_time between 1 and 365*/

/*group by one.id;*/

quit;

[user1]

Thanks so much! I've got the right table I wanted.

Best regards.