Desktop productivity for business analysts and programmers

code help

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

code help

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!


Accepted Solutions
Solution
‎11-13-2012 04:16 PM
Super User
Posts: 18,997

Re: code help

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;

View solution in original post


All Replies
Super User
Posts: 18,997

Re: code help

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;

Occasional Contributor
Posts: 16

Re: code help

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.

Solution
‎11-13-2012 04:16 PM
Super User
Posts: 18,997

Re: code help

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;

Occasional Contributor
Posts: 16

Re: code help

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

Best regards.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 220 views
  • 0 likes
  • 2 in conversation