BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Amir
PROC Star

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.

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Tom
Super User Tom
Super User

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
PROC Star

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
Barite | Level 11

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
Super User Tom
Super User

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
Diamond | Level 26

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().

--
Paige Miller
Amir
PROC Star

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:

 

Amir_0-1705996422978.png

 

 

Thanks & kind regards,

Amir.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 724 views
  • 4 likes
  • 4 in conversation