how to use a proc delete inside of a if statement

[alepage]

How to use a proc delete in a if statement.

In our case, if Flag eq '0' then delete class_org;

%let flag=0;
data class class_org;
set sashelp.class;
	if &flag eq '1' then
	do;
		output class_org;
		if age eq 15 then age=25;
		output class;
	end;
	else;
	do;
		proc delete lib=work data=class_org;
		run;
		output class;
	end; 
	
run;

[PaigeMiller]

PROCs cannot be run "inside" a DATA step.

If you have the macro variable &FLAG, you could easily turn this into a macro %IF statement

%let flag=0;
%if &flag=1 %then %do;
data class class_org;
    set sashelp.class;
    output class_org;
    if age eq 15 then age=25;
    output class;
run;
%end;
%else %do;
proc delete lib=work data=class_org;
run;
quit;
%end;

Please remember that to branch on macro variable values, you need a macro %IF and not a DATA step IF.

Also, please understand that in your original code

if &flag eq '1'

seems doomed to fail. If &FLAG=0, it is not equal to '1' and if &FLAG=1, it is not equal to '1'. See if you can understand why I said these are not equal.

[Tom]

Note that it will work with an IF statement since SAS will convert types automatically.  But it  will not work in macro %IF code since the macro processor just compares strings.

1    data _null_;
2      if 1 = '1' then put 'equal';
3      else put 'not equal';
4    run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      2:10
equal
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


5
6    %if 1 = '1' %then %do; %put equal; %end;
7    %else %do; %put not equal; %end;
not equal

[Amir · Accepted solution]

Hi,

You cannot invoke a procedure in the middle of data step, so you can try checking the value before executing any Base SAS code.

For example, you can try something like the following untested code. Also note that as the &flag value has no quotes saved with it when you initialise it, you do not need the quotes around the 1 when testing for equality.

%if &flag = 1 %then
%do;
  /* data step code here */
%end;
%else
%do;
  /* proc delete code here */
%end;

Thanks & kind regards,

Amir.

Edit: Changed 0 to 1 in explanation.

[alepage]

Here's my final code. Thank for your help.

Data _null_;
%global CorrectionFlag;
%let CorrectionFlag = 0;
set TEMP.PREMHF;
if (cie eq '8' and broker_group_ind eq 'HW' and branch eq '89') Then 
do;
	%let CorrectionFlag = 1;
end;
RUN;
/*%let CorrectionFlag=0;*/
%put &=CorrectionFlag;

Data temp.premhf temp.premhf_org;
set temp.premhf;
if &CorrectionFlag eq 1 then
do;
	output temp.premhf_org;
	if (cie eq '8' and broker_group_ind eq 'HW' and branch = '89') then 
	do;
		branch = '86';		
	end;
	output temp.premhf;
end;
else 
do;	
	output temp.premhf;
end;
run;
%put &=CorrectionFlag;
%if &CorrectionFlag eq 0 %then
%do;
	proc delete library=temp data=premhf_org;
	run;
%end;

[Tom]

That first step WILL NOT WORK.

MACRO code executes BEFORE the data step even gets compiled.  Definitely before it can run. So you ran this code:

%global CorrectionFlag;
%let CorrectionFlag = 0;
%let CorrectionFlag = 1;
data _null_;
  set TEMP.PREMHF;
  if (cie eq '8' and broker_group_ind eq 'HW' and branch eq '89') then do;end;
run;

So the macro variable CorrectionFlag is always going to be set to 1.

If you want the data step logic to change the value of the macro variable you need to use CALL SYMPUTX().  And there is no need to set it more than once.

%global CorrectionFlag;
%let CorrectionFlag = 0;
data _null_;
  set TEMP.PREMHF;
  if (cie eq '8' and broker_group_ind eq 'HW' and branch eq '89') then do;
    call symputx('CorrectionFlag','1');
    stop;
  end;
run;

But since you are going to use it to generate the DELETE step after the main step you don't need that extra data step at all.

%let &CorrectionFlag=0;

data temp.premhf temp.premhf_org;
  set temp.premhf;
  output;
  if (cie eq '8' and broker_group_ind eq 'HW' and branch = '89') then do;
    call symputx('CorrectionFlag','1');
    output temp.premhf_org;
    branch = '86';  
    output temp.premhf;
  end;
  else do; 
    output temp.premhf;
  end;
run;

%if &CorrectionFlag eq 0 %then %do;
proc delete library=temp data=premhf_org;
run;
%end;

But if you do know in advance whether or not you need the second dataset you can use macro logic to skip making it in the first place.

%if &CorrectionFlag eq 1 %then %do;
data temp.premhf temp.premhf_org;
  set temp.premhf;
  output;
  if (cie eq '8' and broker_group_ind eq 'HW' and branch = '89') then do;
    output temp.premhf_org;
    branch = '86';  
    output temp.premhf;
  end;
  else do; 
    output temp.premhf;
  end;
run;
%end;

But why not just do what you want directly?  It is pretty simple using PROC SQL.

proc sql;
create table temp.premhf_org as
  select * from temp.premhf
  where (cie eq '8' and broker_group_ind eq 'HW' and branch = '89') 
;
%if &sqlobs %then %do;
update temp.premhf
  set branch='86' 
  where (cie eq '8' and broker_group_ind eq 'HW' and branch = '89') 
;
%end;
%else %do;
drop table temp.premhf_org;
%end;
quit;

[PaigeMiller]

Does this work? I can't see how it would possibly work.

I don't have your data set, but try these variations of your code on SASHELP.CLASS. The first one, where name='Janet', should return a 1 because her weight is >60 and her sex is 'F'. The second one, where name='Alfred', should return a zero because Alfred is not 'F', but it returns a 1. The third one, where the person's name is not in the data set, also returns a 1. No matter what is in the data set, correctionflag will be 1.

/* Program 1 */
Data _null_;
%let CorrectionFlag = 0;
set sashelp.class(where=(name='Janet'));
if weight > 60 and sex='F' Then 
do;
	%let CorrectionFlag = 1;
end;
RUN;

%put &=correctionFlag;

/* Program 2 */
Data _null_;
%let CorrectionFlag = 0;
set sashelp.class(where=(name='Alfred'));
if weight > 60 and sex='F' Then 
do;
	%let CorrectionFlag = 1;
end;
RUN;

%put &=correctionFlag;

/* Program 3 */
Data _null_;
%let CorrectionFlag = 0;
set sashelp.class(where=(name='Warren'));
if weight > 60 and sex='F' Then 
do;
	%let CorrectionFlag = 1;
end;
RUN;

%put &=correctionFlag;

Earlier I showed working code to allow you to run a PROC DELETE if certain conditions are true. You have changed that code in significant ways, and it won't work now.

%LET does not work inside a data step the way you think it does. If you need to get data step variable values into a macro variable, you could use CALL SYMPUTX().

[Amir]

Hi @alepage ,

The fact that macro code is executed separately (before) data step code can be seen in the following data step which ends with run cancel; to prevent the data step from being executed, but the macro code is executed:

Thanks & kind regards,

Amir.