sas update table if then procedure

[Crubal]

Hi,

I have initial code which is in a macro function where vairable 'Records' is defined before. 

%if &Records > 5 %then %do;
data Work.Table_1(replace=yes);
set Work.Table_2;
run;
%end;

Table_1 already exists. And if 'Records > 5' then replace it with Table_2; and Table_1 remains if 'Records <= 5'. 

My question is: I am not using macro function, but general procedure (data step). How could I write the code? 

Thank you! 

[mkeintz]

First the macro %IF can only be utilized inside a defined macro, which you have not shown.

What are you trying to do?  Are you creating table2 only when some variable has at least one obs in table1 with a value > 5?

[Crubal]

Hi @mkeintz

Thanks! 

It is within a macro function that I omitted the part. 

%macro step_1;

...

%if &Records > 5 %then %do;
data Work.Table_1(replace=yes);
set Work.Table_2;
run;
%end;

%mend;

%step_1;

I would like to replace Table_1 if 'Recodrs>5' in Table_2, where 'Records' is a variable defiend before; and not replace Table_1 if this condition not satisfied. 

Thanks! 

[ShiroAmada]

Try this...

%macro ReplaceWhenTrue;
%if %eval(&Records>5) %then %do;
  data table1;
     set table2;
  run;
%end;
%mend;

%ReplaceWhenTrue;

Hope this helps.

[Crubal]

Thanks @ShiroAmada

This works. Can I write the code which is the same as you did without using a macro function?

Thank you!

[error_prone]

---

@Crubal wrote:

Thanks @ShiroAmada

This works. Can I write the code which is the same as you did without using a macro function?

Thank you!

---

During compilation each set statement fills the pdv with the variables of the datasets listed in the statement. I don't know any way to circumvent this. If both datasets have the same structure you can use 

data table1;   
   if _nobs_ > 5 then 
      set table2 nobs=_nobs_;
   else 
      set table1;
run;

This step always rewrites table1 ... not the best idea.

[Crubal]

Hi @error_prone

Thanks! I like the idea. As you mentioned they have the same data structure. 

And I changed the code a little bit, because 'table1' may not exist for the first time, but it will exist after running this part:

data table1;   
   if _nobs_ > 5 then 
      set table2 nobs=_nobs_;
run;

In this way, 'table1' does not exist initially, so it is same as 'table2'. And after updating, 'table1' will become 'table2' if _nobs_ > 5.

Is that right? 

Thank you! 

[error_prone]

---

@Crubal wrote:

In this way, 'table1' does not exist initially, so it is same as 'table2'. And after updating, 'table1' will become 'table2' if _nobs_ > 5.

Is that right? 

Thank you! 

---

You could just test your code and see what's happening 😉

I would not recommend removing the else-statement. 

In a production process, i would use either use the solution posted by @Tom or keep the macro.

[Crubal]

Got it, thanks! 

[Tom]

A simple way to conditionally generate code when not using a macro is to use CALL EXECUTE.  So assuming that you still have the macro variable RECORDS defined as before then you could use this data step to conditionally generate the data step that replaces TABLE_1.

data _null_;
  if  &Records > 5 then do;
    call execute(
'data Work.Table_1(replace=yes);
 set Work.Table_2;
 run;
'
    );
  end;
run;