IF THEN DO with MACRO

[bhca60]

I'm trying to get this if then do statement to work with a lookup macro.  Does the macro have to be in multiple places throughout this data step or can I just use it one time? And if so, where does it go? I assume it goes where I have them, but then I don't get all columns after the macro does a lookup of the id and dttm so I'm wondering if the data step is in the order it should be when a macro is being used in an if then do statement.

DATA work.wf_mcd
     work.wf_mcd_user
     work.wf_mci (KEEP=id_key)
     ;
  SET mcd.asign;

  dts_tm = put(cats(userid, "_", put(datepart(dttm), yymmddn8.)), $mdm_rda_dts_team.);  
 if dts_tm in ("MCD")
    THEN DO; 
    OUTPUT work.wf_mcd;

      IF PUT(userid, $mcd_wq_list.) ^= "#"
        THEN OUTPUT work.wf_mcd_user;
    END;

  dts_tm = put(cats(userid, "_", put(datepart(dttm), yymmddn8.)), $mdm_rda_dts_team.);  
 if dts_tm =: 'MCI'

    THEN OUTPUT work.wf_mci;
  %dts_lookup(userid, dttm);
RUN;

[PaigeMiller]

It would be very helpful if you showed us the code for the macro %dts_lookup, otherwise how can we answer your questions?

It would also be very helpful if you showed us (a portion of) your data, provided as working SAS data step code (examples and instructions) and not as file attachments.

It would also be very helpful if you showed us the desired output from running this code on your example data.

In general, you can put the macro anywhere you want, as long as when it resolves it creates legal valid working SAS code that does what you want. This is incredibly important, because if it doesn't produce working SAS code that does what you want, then you don't get the results you want, you will likely get errors and it won't execute. This is also important because people tend to overlook this and then the macro doesn't work.

You can use the macro in your program once or twice or as many times as you want.

[Quentin]

When you call a macro, it generates SAS code, presumably DATA step code in this case.  The code it generates is placed at the point of the macro call.

Just looking at your code, it's impossible to know the problem, because you haven't shown us the macro definition.  But that said, you've place the macro call after the OUTPUT statements in your code, which would be unusual.  Usually an OUTPUT statement is at the bottom of a DATA step.  So you might try:

DATA work.wf_mcd
     work.wf_mcd_user
     work.wf_mci (KEEP=id_key)
     ;
  SET mcd.asign;
  %dts_lookup(userid, dttm);

  dts_tm = put(cats(userid, "_", put(datepart(dttm), yymmddn8.)), $mdm_rda_dts_team.);  
 if dts_tm in ("MCD")
    THEN DO; 
    OUTPUT work.wf_mcd;

      IF PUT(userid, $mcd_wq_list.) ^= "#"
        THEN OUTPUT work.wf_mcd_user;
    END;

  *why is this line here twice? ;
  dts_tm = put(cats(userid, "_", put(datepart(dttm), yymmddn8.)), $mdm_rda_dts_team.);  
 if dts_tm =: 'MCI'

    THEN OUTPUT work.wf_mci;
RUN;

and see if you get lucky.  But it would be better to look at the macro definition, think through what it is doing, and then decide where the macro  invocation should go.  Or maybe you can decide to remove the macro invocation and just paste the code you want.  The macro language can be tricky to learn, and if you're still learning DATA step programming, it's probably better to wait on learning the macro language.

[ballardw]

What sort of "look up" are you doing. If your macro dts_lookup contains any PROC or DATA statements then they will terminate the running data step prematurely.

Examine your LOG with the Option mprint; set prior to execution to see the code generated and any related messages in proximity to other statements. If you don't understand the result then share the LOG with us by copying the text from the log window and pasting into a text box opened on the forum.

A macro call like this generates code in the sequence it appears in the code. The the generated code would appear after the statement:

THEN OUTPUT work.wf_mci;

Without the code of the macro, and quite possibly a clear description of what it does, then it is very hard to tell where it may go in the code.

Note: macros generally to do see the values of data step variables. So if your macro expects to use the values of the variable userid from the data set mcd.asign you may have to approach the problem differently.

[Patrick]

@bhca60 One way to debug: Use option mprint and then inspect the log to see what SAS code where the macro generated. 

The macro executes before the data step executes. It then resolves and generates text that in your case should be valid SAS data step syntax. 

The generated syntax will get executed at the place in your data step code where you call the macro.

If you run below sample code....

%macro demo();
  flag=1;
%mend;

options mprint;

data test;
  set sashelp.class;
  if name='Louise' then 
    do;
      %demo();
    end;
run;

options nomprint;

....then the SAS log shows you what SAS syntax where has been generated and executed.

Just do the same with your code.