topic Re: Insert a unique record within macro in SAS Procedures

Insert a unique record within macro

Crubal — Thu, 18 Jan 2018 21:30:17 GMT

Hi,

I have a question to insert a record to table 'sas_gbl_input_parm' within macro function.

The table has 4 columns: parm_nm, value, location, description.

%macro setrec (parm_nm, value, location, desc);

%if %length(&location)=0 %then %let location=_ALL_;
	%let default_text=;
	%let locid_text=;

	proc sql noprint;

		select parm_val into :default_text from sas_gbl_input_parm
			where upcase(parm_nm)=upcase("&parm_nm")
			  and current_ind = 'Y'
			;
	quit;

	proc sql noprint;

/* Insert a record to previous table no matter parm_nm exists */

	%if &location ne _ALL_ and %length(&locid_text)=0
	   %then %do; 
					insert into sas_gbl_input_parm
						set parm_nm = "&parm_nm"
						  , htl_cd = "&location"
				   		  , parm_val = "&value"
						  , current_ind = 'Y'
						  , lst_updt_usr_id = "User_1"
						  , lst_updt_ts = datetime()
					;
	         %end;
        quit;

%mend setrec;

However, in this way, we will insert a record whether initial 'parm_nm' exists or not;

How can I modify the code so that if the table already contains 'parm_nm' of the new record, it will just update the value of initial record; and if the table does not contain 'parm_nm' of the new record, it will insert a new row of 4 columns to prior table.

Thanks!

Re: Insert a unique record within macro

novinosrin — Thu, 18 Jan 2018 22:00:40 GMT

Do you mean you have an UPSERT situation?

If yes, you are better off pushing the query to in database processing rather than doing at SAS level, which i think is rather tedious.

Suppose your database is oracle, you have excellent options deal with UPSERT situations. I guess for OLTP processing, bringing to SAS environment is prolly not the best practice. My 2 cents

This link does have a SAS pseudo code if you wanna have a look

https://stackoverflow.com/questions/11743778/similar-statement-to-merge-sql-in-sas

Re: Insert a unique record within macro

Crubal — Thu, 18 Jan 2018 22:17:20 GMT

Thanks! As you mentioned it is a table from Oracle database. I will look into the link you sent to me.

The reason why I am using SAS macro is because this is part of the product (together with other code) and the rest is all SAS code. So that I would like to make them consistent.

Re: Insert a unique record within macro

s_lassen — Fri, 19 Jan 2018 08:35:21 GMT

One possibility would be to try the update first, and check if any observations were updated:

%if &location ne _ALL_ and %length(&locid_text)=0
   %then %do;
  update sas_gbl_input_parm
    set htl_cd = "&location"
     , parm_val = "&value"
     , current_ind = 'Y'
     , lst_updt_usr_id = "User_1"
     , lst_updt_ts = datetime()  
  where parm_nm = "&parm_nm"  
  ;
  %if &sqlobs=0 %then %do; /* no records updated */
  insert into sas_gbl_input_parm
     set parm_nm = "&parm_nm" 
     , htl_cd = "&location"
     , parm_val = "&value"
     , current_ind = 'Y'
     , lst_updt_usr_id = "User_1"
     , lst_updt_ts = datetime()  
  ;
  %end;
%end;

Re: Insert a unique record within macro

Crubal — Fri, 19 Jan 2018 18:07:04 GMT

Thank you!