BookmarkSubscribeRSS Feed
Crubal
Quartz | Level 8

Hi, 

 

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';
		end;

		else do;
			Step_ID = 002;
			Error_Code = 'SUC001';
		end;
                output;
run;

 

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;
		datalines;
	run;

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
				set 
					proc_step_end_ts = datetime()
					, proc_step_stat_cd = "Failed"
					, lst_updt_ts = datetime()
				where proc_log_id = &proc_log_id;
			%end;

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

%etl_error_log;

 

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! 

6 REPLIES 6
Kurt_Bremser
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.

Crubal
Quartz | Level 8

Thanks @Kurt_Bremser

 

Absolutely right. It can work after adding this syntax. 

RW9
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;
quit;

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?  

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

 

 

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

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
  • 6 replies
  • 976 views
  • 0 likes
  • 3 in conversation