Insert a unique record within macro

[Crubal]

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! 

[novinosrin]

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

[Crubal]

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. 

[s_lassen]

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;

[Crubal]

Thank you!