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!
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;
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;
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.
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;
Thanks so much! I've got the right table I wanted.
Best regards.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.