Help using Base SAS procedures

Insert a unique record within macro

Reply
Regular Contributor
Posts: 154

Insert a unique record within macro

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! 

 

PROC Star
Posts: 1,836

Re: Insert a unique record within macro

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

 

Regular Contributor
Posts: 154

Re: Insert a unique record within macro

Posted in reply to novinosrin

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. 

PROC Star
Posts: 266

Re: Insert a unique record within macro

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;
Regular Contributor
Posts: 154

Re: Insert a unique record within macro

Thank you! 

Ask a Question
Discussion stats
  • 4 replies
  • 221 views
  • 2 likes
  • 3 in conversation