topic Re: Rename variable only if it exists in SAS Programming

Rename variable only if it exists

Babloo — Tue, 26 Nov 2019 11:57:31 GMT

I would like to rename one variable only if it exists otherwise I should leave as such. In the below example, I'm getting error if variable 'id' doesn't exist. But I would like to conditionally execute the rename statement to get rid of the error.

data want;
set test; /*id variable sometimes exists with 'id' otherwise it will be like 'employee_id'*/ rename id=employee_id; /*rename statement should run only 'id' variable is available*/ run;

Re: Rename variable only if it exists

PeterClemmensen — Tue, 26 Nov 2019 12:04:05 GMT

Can you post your log please? I doubt that this gives your an error. Possibly a warning.

Re: Rename variable only if it exists

s_lassen — Tue, 26 Nov 2019 12:08:48 GMT

You could use SQL, like this:

%let rename=; /* initialize, otherwise macro variable will not exist if no id variable */
proc sql noprint;
  select 'rename ID=Employee_id;' into :rename
  from dictionary.columns
  where libname='WORK' and memname='TEST' and name='ID';
quit;

data want;
  set test;
  &rename
run;

Re: Rename variable only if it exists

Babloo — Tue, 26 Nov 2019 13:02:18 GMT

Can we do in data step?

Re: Rename variable only if it exists

andreas_lds — Tue, 26 Nov 2019 13:20:14 GMT

@Babloo wrote:
Can we do in data step?

Yes, of course, but the data-step will have to generate another data-step to do the renaming. This has been asked and answered so many times, please use the search-function to find code.

Re: Rename variable only if it exists

Tom — Tue, 26 Nov 2019 14:44:29 GMT

So if ID doesn't exist you just want to copy the input dataset without any renaming?

You can probably just change the DKRICOND option setting.

Example:

862   options dkricond=warn;
863
864   data want;
865    set sashelp.class (rename=(id=employee_id));
WARNING: Variable id is not on file SASHELP.CLASS.
WARNING: The variable id in the DROP, KEEP, or RENAME list has never been referenced.
866    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.WANT has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


867   options dkricond=nowarn;
868
869   data want;
870    set sashelp.class (rename=(id=employee_id));
871    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.WANT has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


872   options dkricond=error;
873
874   data want;
875    set sashelp.class (rename=(id=employee_id));
ERROR: Variable id is not on file SASHELP.CLASS.
ERROR: Invalid DROP, KEEP, or RENAME option on file SASHELP.CLASS.
876    run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0 observations
         and 0 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: Rename variable only if it exists

Babloo — Tue, 26 Nov 2019 15:16:18 GMT

Thanks. Let me slightly tweak the question. In your example, assume you are
calling one macro variable called &macrovariable. after SET statement
followed by rename statement as follows.

Data want;
set have;
&macrovariable.; /*resolves to some IF clause otherwise it will not resolve
to any value*/
rename id=employee_id;
run;

Now I want the above data step to be completed without any error if the
&macrovarible not resolves to any value.

Re: Rename variable only if it exists

Tom — Tue, 26 Nov 2019 15:25:51 GMT

@Babloo wrote:
Thanks. Let me slightly tweak the question. In your example, assume you are
calling one macro variable called &macrovariable. after SET statement
followed by rename statement as follows.

Data want;
set have;
&macrovariable.; /*resolves to some IF clause otherwise it will not resolve
to any value*/
rename id=employee_id;
run;

Now I want the above data step to be completed without any error if the
&macrovarible not resolves to any value.

Note there is a different option, DKROCOND, for drop/keep/rename options on OUTPUT datasets.

options dkrocond=nowarn;
data want(rename=(id=employee_id));
  set have;
  &macrovariable.;
run;

But it really dounds like you are trying to do this:

data want;
  set have;
%if %length(%superq(macrovariable)) %then %do;
  &macrovariable.;
  rename id=employee_id;
%end;
run;

Re: Rename variable only if it exists

Babloo — Tue, 26 Nov 2019 15:48:18 GMT

Right!

Why we need %length macro?

Re: Rename variable only if it exists

ballardw — Tue, 26 Nov 2019 15:49:09 GMT

@Babloo wrote:
Thanks. Let me slightly tweak the question. In your example, assume you are
calling one macro variable called &macrovariable. after SET statement
followed by rename statement as follows.

Data want;
set have;
&macrovariable.; /*resolves to some IF clause otherwise it will not resolve
to any value*/
rename id=employee_id;
run;

Now I want the above data step to be completed without any error if the
&macrovarible not resolves to any value.

RENAME is declarative, not executable. That means that it is not available conditionally. What ever is supposed to generate your "macro variable" would have to conditionally create either complete rename statement syntax or blank text.

Re: Rename variable only if it exists

Tom — Tue, 26 Nov 2019 16:09:30 GMT

If the macro variable is empty then its length is zero. You can use whatever test you want to check if the macro variable actually has a value or not. Might be a better design to have whatever process created the macro variable help. Either by conditionally adding the RENAME statement to the value of the macro variable. Or by setting some other macro variable that will be easier to test that tells whether or not the RENAME statement needs to be generated.