topic Re: How to add new row with dynamic data in existing SAS data set in SAS Programming

How to add new row with dynamic data in existing SAS data set

Gagan_s — Thu, 05 Sep 2019 06:52:58 GMT

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

Re: How to add new row with dynamic data in existing SAS data set

ErikLund_Jensen — Thu, 05 Sep 2019 07:50:59 GMT

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;

Re: How to add new row with dynamic data in existing SAS data set

hashman — Thu, 05 Sep 2019 22:21:12 GMT

@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.

Re: How to add new row with dynamic data in existing SAS data set

Gagan_s — Fri, 06 Sep 2019 02:43:00 GMT

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'?

Re: How to add new row with dynamic data in existing SAS data set

Gagan_s — Fri, 06 Sep 2019 06:31:47 GMT

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))

Re: How to add new row with dynamic data in existing SAS data set

ErikLund_Jensen — Fri, 06 Sep 2019 07:57:35 GMT

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?