BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gagan_s
Fluorite | Level 6

I need to insert a new row into an existing SAS data set. Data set has column as:

 

 

Name		Type
Id		Numeric

Data that I want to insert (as a new row) is dynamic. Hence I want my solution to accept the data at run time and then insert as a new row into the data set.

 

I did some search and came to know that I can set some environment variable at run time and then my SAS program can read those value to insert into the SAS data set. I did something like this:

export Id_VAL=5
proc SQL;
  insert into LibName.dataset values (
	input(sysget('Id_VAL'),best.), 
);
quit;

However upon running the above code I am getting:

38         	input(sysget('Id_VAL'),best.),
            _____
            22
            76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, 
              a missing value, +, -, MISSING, NULL, USER.  

I also checked on creating another data set and then merging with the main data set using proc append command but not sure how I can pass values at run time.

I am a newbie in SAS hence I am not sure if this is the right solution.

 

SAS version 9.4

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @Gagan_s 

 

You are almost there. I used a couple of existing environment variables in the following examples:

 

****** Test with numeric value ****;
* Initiate data set;
data ds; 
	PROCESSOR_LEVEL = 1; 
run;

* Add value of numeric environment variable;
proc SQL;
  insert into ds values (%sysfunc(sysget(PROCESSOR_LEVEL)));
quit;

****** Same test with character value ****;
* Initiate data set;
data ds; 
	USERDOMAIN = 'kfhadjkhajkdfhasdkfhahk'; 
run;

* Add value of character environment variable - note use of quotes;
proc SQL;
  insert into ds values ("%sysfunc(sysget(USERDOMAIN))");
quit;

 

 

View solution in original post

5 REPLIES 5
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @Gagan_s 

 

You are almost there. I used a couple of existing environment variables in the following examples:

 

****** Test with numeric value ****;
* Initiate data set;
data ds; 
	PROCESSOR_LEVEL = 1; 
run;

* Add value of numeric environment variable;
proc SQL;
  insert into ds values (%sysfunc(sysget(PROCESSOR_LEVEL)));
quit;

****** Same test with character value ****;
* Initiate data set;
data ds; 
	USERDOMAIN = 'kfhadjkhajkdfhasdkfhahk'; 
run;

* Add value of character environment variable - note use of quotes;
proc SQL;
  insert into ds values ("%sysfunc(sysget(USERDOMAIN))");
quit;

 

 

Gagan_s
Fluorite | Level 6

Amazing. Thanks a lot. Just a small related question, how would I do the same thing in the column type is Numeric with informat is 'DATETIME21'?

Gagan_s
Fluorite | Level 6

found the solution for inserting DATETIME21 date in existing stored proc (reading value from env variable)

export PROCESS_DTTM=\'07AUG2019:15:04:53\'dt

and then inserting the data in stored proc like:

%sysfunc(sysget(PROCESS_DTTM))
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @Gagan_s 

 

Time is 9.53 here, and I'm busy at work right now, but I will look into it later today. 

 

Would you please post an example of an actual value?

hashman
Ammonite | Level 13

@Gagan_s:

You can keep your input/sysget syntax, but then you have to add the record in the DATA step:

data libname.dataset ;                                                                                                                                                                                                                                          
  id = input (sysget ("id_val"), best.) ;                                                                                                                                                                                                                       
  output ;                                                                                                                                                                                                                                                      
  stop ;                                                                                                                                                                                                                                                        
  modify libname.dataset ;                                                                                                                                                                                                                                      
run ;                     

Of course, you can do it this way (not recommended😞 

data libname.dataset ;      
  set libname.dataset end = z ;  
output ; if z ; id = input (sysget ("id_val"), best.) ; output ; run ;

However, this code rereads the entire data set and then rewrites it with 1 observation added. By contrast, the step with MODIFY just adds the new record to the existing data set without reading any observations in - essentially just the way proc APPEND does. Speaking of which, you can of course use it as an alternative:

data new ;
  id = input (sysget ("id_val"), best.) ;
run ;

proc append base = libname.dataset data = new ;
run ;

Kind regards

Paul D.  

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 5323 views
  • 2 likes
  • 3 in conversation