BookmarkSubscribeRSS Feed
Quartz | Level 8



I have a question changing this 'data step' procedure to 'proc sql' procedure.


Data Step Procedure: ('num_rec_prop' is a parameter that I have set and called)


data Process_Step_Error_Log;
		if &num_rec_prop NE 1 then do;
			Step_ID = 001;
			Error_Code = 'AEE001';

		else do;
			Step_ID = 002;
			Error_Code = 'SUC001';


However, when I change this procedure into 'proc sql' and uses macro function. The reason is if there is an error, another table needs to be updated as well (PROC_STEP_LOG, and this table exists initially)


I create an empty table first:

	data Proc_Step_Error_Log;
		input Step_Id 10 Error_Code $10;

Then, I use the following macro procedure: (proc_log_id is read and called before as well) 

%macro etl_error_log;
		%if &num_rec_prop NE 1 
			%then %do;
			insert into Proc_Step_Error_Log
				set Step_Id = 001
				  , Error_Code = "AEE001";

                          update PROC_STEP_LOG
					proc_step_end_ts = datetime()
					, proc_step_stat_cd = "Failed"
					, lst_updt_ts = datetime()
				where proc_log_id = &proc_log_id;

                 %else %do;
			insert into Proc_Step_Error_Log
				set Step_Id = 002
				  , Error_Code = "SUC001";



If I ran this part, error message shows up:

ERROR 180-322: Statement is not valid or it is used out of proper order. (Highlight on the word 'insert')


How could I fix my code to run successfully?


Thank you! 

Super User

I see no proc sql statement, and no corresponding quit; to end the proc sql.

Strong hint: always get your code to run without macro action before making it dynamic.

Quartz | Level 8

Thanks @Kurt_Bremser


Absolutely right. It can work after adding this syntax. 

Diamond | Level 26 RW9
Diamond | Level 26

There really is no need for any macro code here.  Why do you need to change this into SQL, are you connecting to a database?  You can do it simply with a case:

proc sql;
  insert into etl_error_log
  set  stepid=case when &num_rec_prop. ne 1 then "001" else "002" end,
       error_code=case when &num_rec_prop. ne 1 then "AEE001" else "SUC001" end;

However that being said, I would question why you are doing this in the first place.  What is the process, how are you getting the data in?  

Quartz | Level 8

@RW9 Thanks for your reply!

The reason I am changing the data procedure to proc sql is because the tables I try to update is in the Oracle. 



Diamond | Level 26 RW9
Diamond | Level 26

Well, step 1 is deciding where to do the work then.  There is no point in manipulating tables on a database from SAS, either download the data to SAS, process, then upload again, or do the processing on the database.  All your doing is hitting the large overhead of data transmittal back and forth.  If you do this on the database for instance you can set hooks on your dataset, which, when a condition is struck, inserts data automatically into the journal tables.  I would also question why the need for "Error" codes in the first place.  SAS is a data driven language, if no data is read in then the whole process can run and there would be no output, no need to generate an error system to catch these things as the code should run either way.

Quartz | Level 8

Thank you @RW9


The first step I am doing (create an empty table for Proc_Step_Error_Log) is because it does not exist in the Oracle database initially. So that I want to add this part to avoid error. 


Once the table exists, I will delete step 1 procedure. 



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3 in conversation