topic Re: change data step to proc sql procedure in SAS Procedures

change data step to proc sql procedure

Crubal — Mon, 18 Dec 2017 05:40:51 GMT

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!

Re: change data step to proc sql procedure

Kurt_Bremser — Mon, 18 Dec 2017 07:00:01 GMT

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.

Re: change data step to proc sql procedure

RW9 — Mon, 18 Dec 2017 09:22:13 GMT

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?

Re: change data step to proc sql procedure

Crubal — Mon, 18 Dec 2017 13:27:15 GMT

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

Re: change data step to proc sql procedure

Crubal — Mon, 18 Dec 2017 13:28:51 GMT

Thanks @Kurt_Bremser

Absolutely right. It can work after adding this syntax.

Re: change data step to proc sql procedure

RW9 — Mon, 18 Dec 2017 13:45:05 GMT

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.

Re: change data step to proc sql procedure

Crubal — Mon, 18 Dec 2017 14:07:35 GMT

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.