topic Re: Dynamically rename a table's variable names with another table's variable values in SAS Programming

Dynamically rename a table's variable names with another table's variable values

Zeus_Olympus — Thu, 30 Jun 2022 07:27:31 GMT

Dear all,

I have table A with two variables RET_VAR and CODE having values as shown below:

RET_VAR    CODE
Blue	   345V
Green	   678V
Orange	   546V
White	   879V
Black	   234V
Yellow	   987V
Purple	   234V

and a table B with many variables Var(i) with names:

   Black	   Blue	          Brown	          Gray	          Green	         Maroon	         Olive	          Orange	   Pink	         Purple	         Silver   	White	         Yellow
 3.456,00 	 4.792,20 	 5.901,60 	 6.784,20 	 7.440,00 	 7.869,00 	 8.071,20 	 8.046,60 	 7.795,20 	 7.317,00 	 9.362,80 	 2.314,00 	 3.906,20 
 5.634,00 	 5.067,00 	 4.500,00 	 3.933,00 	 3.366,00 	 2.799,00 	 2.232,00 	 1.665,00 	 1.098,00 	 531,00 	 2.345,00 	 1.778,00 	 1.211,00 
 4.758,00 	 4.191,00 	 3.624,00 	 3.057,00 	 2.490,00 	 1.923,00 	 1.356,00 	 789,00 	 222,00 	 6.532,00 	 5.965,00 	 5.398,00 	 4.831,00

I need to rename the variables in table B that are ONLY included in table A with the values of variable CODE in table A.

Any suggestions would be welcomed.

Thank you

Re: Dynamically rename a table's variable names with another table's variable values

andreas_lds — Thu, 30 Jun 2022 07:15:55 GMT

Please post the expected output and data in usable form.

Re: Dynamically rename a table's variable names with another table's variable values

Zeus_Olympus — Thu, 30 Jun 2022 07:28:01 GMT

Done.
Thanks!

Re: Dynamically rename a table's variable names with another table's variable values

Reeza — Thu, 30 Jun 2022 15:01:09 GMT

Note that those are awkward SAS variable names so you'll need to refer to them as '345V'n in future usage.
Perhaps a label may be a better approach?

This will change the names.


proc sql;
create table change_list as
select * from tableA
where upper(ret_var) in (select trim(upper(name)) from sashelp.vcolumn where libname='WORK' and memname='TABLEB');
quit;


data _null_;
set change_list end=eof;

if _n_ = 1 then 
call execute ('proc datasets lib=work nodetails nolist; modify TableB; rename ');

call execute (catt(ret_var, ' = ', nliteral(code)));
call execute (' ');
if eof then call execute(';run; quit;');
run;

To create labels instead, change the word rename to LABEL and remove the NLITERAL function. If you're trying to export the data or have it in a report, labels are easier to work with.

data _null_;
set change_list end=eof;

if _n_ = 1 then 
call execute ('proc datasets lib=work nodetails nolist; modify TableB; label ');

call execute (catt(ret_var, ' = ', code));
call execute (' ');
if eof then call execute(';run; quit;');
run;

Re: Dynamically rename a table's variable names with another table's variable values

andreas_lds — Fri, 01 Jul 2022 05:02:30 GMT

Black an Purple have the same value, so renaming won't work and as @Reeza already said, using names like "234V" forces you to use the hardly readable '234V'n all the time => you don't want this.