SAS Calling ProcSQL-Macro in Data Step

Question

I cannot run my PROC SQL function by calling the macro in my Data step. The SQL function alone works, but I need to let it run for every Security Group.

%macro adding;
        proc sql;
        insert into have (Time,seconds) values
        ("9:10:00"t,33000);
        insert into have (Time,seconds) values
        ("16:50:00"t,60600);
        quit;
%mend;

data have;
set have;
by security;
if first.security then %adding;
if seconds=33000 then date=lag(date);
if seconds=60600 then date=lag(date);
run;

The error is:

1 proc sql; insert into have (Time,seconds) values

---- ------
   180               180 180 180 1   ! ("9:10:00"t,33000);         insert into have (Time,seconds) values 1   !
("16:50:00"t,60600); quit; ERROR 180-322: Statement is not valid or it is used out of proper order.

I don't know what to change that I can use it...

Thankful for any help! Best

Reeza Reeza · Accepted Answer · 2016-08-13T05:54:18

Use call execute to call the macro.

If first.security then call execute('%adding');

However, the macro will run AFTER the data step, not during. Also, trying to change the data in place that many ways could lead to difficulties in debugging. Your DATA, SET, and SQL all reference the same data set.

If you're trying to change the data in your proc and add records you may want to consider using explicit OUTPUT statements within the data step itself. You could use a macro to generate these statements if desired.

 If first.security then do;
     Time=...;
      Seconds=....;
     Output;
     Time=....;
      Seconds=....;
      Output;
  End;

   *rest of SAS code;
   Output; Add an explicit output if required;
   Run;

You also shouldn't be calculating a lagged value conditionally, as the lag is a queue. You'll get unexpected behaviour. I haven't highlighted all the issues with your process but this should be enough to help you find the rest.

SAS Calling ProcSQL-Macro in Data Step

---- ------

1 Answers