topic Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info? in SAS Programming

How to dynamically update SAS Variables based on pre-defined key, Variable name info?

ez123 — Thu, 21 Mar 2019 17:33:08 GMT

I have config file that contains the following info:

source

Field name to be updated

The user might provide me with a specific source information and value to be updated.

I need to read the config file and find out the Field name to be updated and update the corresponding field with the user input value.

Below is my code:

data config;

length source $1 updt_Field $12 ;

input source $ updt_Field $;

datalines;

A field1

B field2

;

run;

data out;

length field1 $20 field2 $20 value $10;

if 0 then set config;

if _N_ = 1 then do;

/* load config data set into the hash object */

declare hash h_src(dataset: 'work.config');

/* define SMALL data set variable K as key and S as value */

h_src.defineKey('source');

h_src.defineData('updt_Field');

h_src.defineDone();

/* avoid uninitialized variable notes */

call missing(source,updt_Field);

end;

value='0000099999';

drop rc;

source='2';

rc = h_src.find();

put updt_Field;

* I want to update the field represented in the updt_Field variable with the value;

vnamex(updt_Field)=value;

run;

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

PeterClemmensen — Thu, 21 Mar 2019 18:00:29 GMT

Does the code do as you expect or what results differ from your expected results? 🙂

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

ez123 — Thu, 21 Mar 2019 18:03:22 GMT

It errors out at the vnamex function. I was kind of hoping the vnamex function would do the job for me but its not the case!!

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

PeterClemmensen — Thu, 21 Mar 2019 18:08:34 GMT

Ok. Just to be clear. The source='2'; statemet, does that indicate that it is the second variable that has to be updated with the given value?

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

ez123 — Thu, 21 Mar 2019 18:21:07 GMT

Actually the statement should be source='A'; which indicates that field1 should be updated with the value "0000099999".

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

noling — Thu, 21 Mar 2019 18:40:28 GMT

How will you know which value to use? Will it always be the '0000099999' as a character?

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

ez123 — Thu, 21 Mar 2019 18:47:26 GMT

For now, we can assume the value would be always constant. but again, I can always read this as input from a user.

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

noling — Thu, 21 Mar 2019 18:53:32 GMT

I'm intrigued using a hash in a datastep to do this. @PeterClemmensen do you know if it's possible to use the vnamex function on the left-hand side of an expression?

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

PeterClemmensen — Thu, 21 Mar 2019 20:17:19 GMT

There are several ways to work around this issue. Here is one.

data config;
   length source $1 updt_Field $12 ;
   input source $ updt_Field $;
   datalines;
A field1
B field2
;
run;

data out;
     length field1 $20 field2 $20 value $10;
     if 0 then set config;
     if _N_ = 1 then do;
     /* load config data set into the hash object */
     declare hash h_src(dataset: 'work.config');
     /* define SMALL data set variable K as key and S as value */
     h_src.defineKey('source');
     h_src.defineData('updt_Field');
     h_src.defineDone();
     /* avoid uninitialized variable notes */
     call missing(source,updt_Field);
     end;

     value='0000099999';
     drop rc;

     source='A';
     rc = h_src.find();

     call symputx('updt_Field', updt_Field);
     &updt_Field.=value;
run;

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

ez123 — Thu, 21 Mar 2019 22:44:13 GMT

symputx doesn't work as coded above. have tried this method before and am getting the same error!

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

Patrick — Fri, 22 Mar 2019 06:30:12 GMT

@ez123

Agree, using symputx() won't work here as the macro variable will only become available after the data step (timing issue).

Eventually one could get around this via dosubl() but I believe that still wouldn't resolve the issue. Basically: The variable assignment code needs to exist during the compilation phase. There is no way to generate such syntax during execution time of the data step.

Below code should do what you want:

data config;
  length source $1 updt_Field $12;
  input source $ updt_Field $;
  datalines;
A field1
B field2
;
run;

data fmtds;
  set config;
  rename source=start updt_field=label;
  retain fmtname '$CodeToVname';
run;
proc format cntlin=fmtds;
run;

/* option 1 */
data fmtds(drop=_:);
  length field1 $20 field2 $20 value $10;
  array _cvars {*} _character_;
  fmtname='VnameToArrElement'; type='I';
  do _i=1 to dim(_cvars);
    start=vname(_cvars[_i]);
    label=_i;
    output;
  end;
  stop;
run;
proc format cntlin=fmtds;
run;
data out;
  length field1 $20 field2 $20 value $10;
  array _cvars {*} _character_;
  source='A';
  value='0000099999';
  _cvars[input(put(source,$CodeToVname.),VnameToArrElement.)]=value;
run;

/* option 2 */
data out(drop=_i);
  length field1 $20 field2 $20 value $10;
  array _cvars {*} _character_;
  source='A';
  value='0000099999';
  do _i=1 to dim(_cvars);
    if vname(_cvars[_i])=put(source,$CodeToVname.) then
      do;
        _cvars[_i]=value;
        leave;
      end;
  end;
run;

If this is some sort of request/response scenario where you only create a single row per table "OUT" then I'd go for Option 2 and I'd also consider to create a permanent format from the Config table (updated via a housekeeping job).

If you've got "many" rows in table 'OUT" to process then I'd go for Option 1.

You might also want to add some "upcasing" to the code I've posted so that codes and variable names match if casing in sources differs.

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

Astounding — Fri, 22 Mar 2019 06:22:49 GMT

As a last resort, you could always split the DATA step in two. Instead of VNAMEX, capture the variable name as a macro variable:

call symputx('varname', Updt_field);

Then use a new DATA step to utilize &VARNAME. However, before resorting to that, let's explore some other lookup methods other than a hash table.

It seems that the user chooses the value to be updated. Presumably, that can be captured as a macro variable, equivalent to:

%let user_choice=A;

Looking at the CONFIG file, the UPDT_FIELD values must be legal SAS variable names. Is the same true for the values of SOURCE? Are they always a single letter (or a set of letters that could be a valid SAS variable name)? If so, the problem lends itself to a different approach. First, convert the CONFIG file to a set of macro variables:

data _null_;

length source $1 updt_Field $12 ;

input source $ updt_Field $;

call symputx(source, updt_field);

datalines;

A field1

B field2

;

Having done that, the look-up is simple:

data out;

&&&user_choice = '0000099999';

run;

The only downside to this approach is that you lose error-handling ability if the user enters a bad value (if the CONFIG file defines "A" and "B", and the user enters "C" instead).

Re: How to dynamically update SAS Variables based on pre-defined key, Variable name info?

ballardw — Fri, 22 Mar 2019 22:07:32 GMT

@noling wrote:

I'm intrigued using a hash in a datastep to do this. @PeterClemmensen do you know if it's possible to use the vnamex function on the left-hand side of an expression?

Pretty sure that use of VNAMEX(anything) = causes an

ERROR: Undeclared array referenced: vnamex.

As will VNAME and all the variable information functions except for the CALL functions. They return some sort of value given a variable name, array reference or for the correct functions, an expression that resolves to a name and can assign that resolved value to another variable