Hey everyone,
I've got 2 columns of data: x (a unique person identifier) and y (a date at which an action was performed by x). Some x'es occur more than once, and thus take up multiple rows. I've got the 2 columns sorted by x then by y so I have a list of all actions for each person's identifier and when they occurred. I would like to count the number of times that a person has performed an action in the past 90 days prior to date y, for all rows. My data (and my desired output) looks something like this:
x y desired output
a1154 7/1/2018 3
a1154 6/22/2018 2
a1154 6/1/2018 1
a1154 5/25/2018 0
a2266 11/11/2018 0
a2266 1/1/2017 0
a1997 5/12/2018 0
a2111 5/18/2016 1
a2111 5/15/2016 0
To do this, I am using lags. I am creating two variables (emp<n> and dd<n>) that use lag1, lag2, ... lag365 to compare each row with the nth row underneath it to see if there is an x match and if y is within the 90 day mark. I am doing this using macros. Here is my code:
macro check;
data macro_output;
set datasource;
do p=1 to 365;
emp&p = lag&p(x);
dd&p = lag&p(y)
end;
mend check;
check;
macro still;
data finaloutput;
set macro_output;
output = 0;
n=1;
do until (n >= Last.x );
do p=1 to 365;
if x = emp&p & y < dd&p+90 then output = p;
end;
n=n+1;
end;
mend still;
still;
run;
When I run this, I get the error "ERROR 180-322: Statement is not valid or it is used out of proper order".
This error is occurring inside "run;", and SAS is telling me that the affected code is "%LET_CLIENTTASKLABEL=;" at line 59 and log line 51.
I have absolutely no idea what this means. From other threads and questions, I have seen this error usually attributed to a missing semicolon or a typo in the code. However, I do not believe this is the case here. I am writing this as a manually coded program inside Enterprise Guide 7.1. Would anyone please be able to help me figure this error out? Thank you.
Hi @jonesj22 I have absolutely no clue or understanding of the objective in having a macro here assuming my understanding of the logic i.e from what you wrote "I would like to count the number of times that a person has performed an action in the past 90 days prior to date y, for all rows"
Assuming i understand this bit
The logic seems far too simple.
data have;
input x $ y :mmddyy10. ;
format y mmddyy10.;
cards;
a1154 7/1/2018 3
a1154 6/22/2018 2
a1154 6/1/2018 1
a1154 5/25/2018 0
a2266 11/11/2018 0
a2266 1/1/2017 0
a1997 5/12/2018 0
a2111 5/18/2016 1
a2111 5/15/2016 0
;
proc sql;
create table want as
select a.*,sum( intnx('days',a.y,-90)<=b.y<a.y) as count
from have a, have b
where a.x=b.x
group by a.x,a.y
order by a.x, a.y desc;
quit;
If I overlooked something, please correct me. Thank you!
place this command at the top of your program and run it again
options mprint symbolgen mlogic;
Then show us the ENTIRE SAS log, not just the error message. To do this, click on the {i} icon and paste the ENTIRE log into the window that appears.
Hi @jonesj22 I have absolutely no clue or understanding of the objective in having a macro here assuming my understanding of the logic i.e from what you wrote "I would like to count the number of times that a person has performed an action in the past 90 days prior to date y, for all rows"
Assuming i understand this bit
The logic seems far too simple.
data have;
input x $ y :mmddyy10. ;
format y mmddyy10.;
cards;
a1154 7/1/2018 3
a1154 6/22/2018 2
a1154 6/1/2018 1
a1154 5/25/2018 0
a2266 11/11/2018 0
a2266 1/1/2017 0
a1997 5/12/2018 0
a2111 5/18/2016 1
a2111 5/15/2016 0
;
proc sql;
create table want as
select a.*,sum( intnx('days',a.y,-90)<=b.y<a.y) as count
from have a, have b
where a.x=b.x
group by a.x,a.y
order by a.x, a.y desc;
quit;
If I overlooked something, please correct me. Thank you!
This is exactly what I was looking to do, thank you. I am not nearly as proficient in SQL querying as I am in coding, and as such I did not even consider this approach to the problem.
Looks like a simple self join will let you calculate that.
data have ;
input x $ y :yymmdd. desired ;
format y yymmdd10.;
cards;
a1154 2018-07-01 3
a1154 2018-06-22 2
a1154 2018-06-01 1
a1154 2018-05-25 0
a2266 2018-11-11 0
a2266 2017-01-01 0
a1997 2018-05-12 0
a2111 2016-05-18 1
a2111 2016-05-15 0
;
proc sql ;
create table want as
select a.*,count(b.y) as count
from have a
left join have b
on a.x=b.x
and b.y between a.y-90 and a.y-1
group by a.x,a.y,a.desired
;
quit;
proc print;
run;
Obs x y desired count 1 a1154 2018-05-25 0 0 2 a1154 2018-06-01 1 1 3 a1154 2018-06-22 2 2 4 a1154 2018-07-01 3 3 5 a1997 2018-05-12 0 0 6 a2111 2016-05-15 0 0 7 a2111 2016-05-18 1 1 8 a2266 2017-01-01 0 0 9 a2266 2018-11-11 0 0
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.