SAS Error 180-322 using a macro.

[jonesj22]

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.

[PaigeMiller]

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.

[novinosrin · Accepted solution]

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!

[jonesj22]

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. 

[Tom]

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