BookmarkSubscribeRSS Feed
Crubal
Quartz | Level 8

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! 

 

4 REPLIES 4
novinosrin
Tourmaline | Level 20

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
Quartz | Level 8

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
Meteorite | Level 14

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
Quartz | Level 8

Thank you! 

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
  • 4 replies
  • 1052 views
  • 2 likes
  • 3 in conversation