topic sas update table if then procedure in SAS Procedures

sas update table if then procedure

Crubal — Thu, 28 Sep 2017 21:38:51 GMT

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!

Re: sas update table if then procedure

mkeintz — Thu, 28 Sep 2017 21:53:37 GMT

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?

Re: sas update table if then procedure

ShiroAmada — Fri, 29 Sep 2017 01:41:22 GMT

Try this...

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

%ReplaceWhenTrue;

Hope this helps.

Re: sas update table if then procedure

Crubal — Fri, 29 Sep 2017 13:24:57 GMT

Thanks @ShiroAmada

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

Thank you!

Re: sas update table if then procedure

Crubal — Fri, 29 Sep 2017 13:37:41 GMT

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!

Re: sas update table if then procedure

error_prone — Mon, 02 Oct 2017 06:13:56 GMT

@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.

Re: sas update table if then procedure

Crubal — Tue, 03 Oct 2017 13:17:43 GMT

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!

Re: sas update table if then procedure

Tom — Tue, 03 Oct 2017 13:57:58 GMT

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;

Re: sas update table if then procedure

error_prone — Tue, 03 Oct 2017 18:58:41 GMT

@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.

Re: sas update table if then procedure

Crubal — Wed, 04 Oct 2017 13:42:10 GMT

Got it, thanks!