Rename dataset variables using Proc LUA

[ftenorio]

Hello,

I was wandering if anyone here has already used Proc Lua to rename all (or a few) variables from a given dataset. I would like to add a prefix, let's say "pref_" to each variable of a dataset, but I'm not being able to do so. The program below shows what I'm trying to accomplish (using SAS 9.4 M5):

data cars;
set sashelp.cars;
run;

/*The procedure itself*/
proc lua restart;
submit;
	local dsid = sas.open("work.cars","u") -- open in update mode
   -- Iterate over the variables in the data set
  for var in sas.vars(dsid) do
		--Try to assign the prefix to the variable name in lower case
		var.name = "pref_"..var.name:lower()
		--List the variable names
		print(var.name)
  end
	sas.close(dsid)
endsubmit;
run;

proc contents data=work.cars;
run;

Does anyone has an idea?

Thanks,

Fernando.

[Kurt_Bremser]

Maxim 14: use the right tools.

The tool for manipulating datasets is proc datasets, and the metadata are stored in the dictionary tables and their views in sashelp.

So you only need to find a way to dynamically create proc datasets code from the metadata, and you do that with call execute():

data cars;
set sashelp.cars;
run;

%let targlib=WORK;
%let targdata=CARS;
%let pref=pref_;

data _null_;
set sashelp.vcolumn (where=(libname = "&targlib." and memname = "&targdata.")) end=eof;
if _n_ = 1 then call execute("proc datasets library=&targlib. nolist;modify &targdata.; rename");
call execute(' ' !! trim(name) !! '=' !! "&pref." !! trim(name));
if eof then call execute('; quit');
run;

This is the log:

24         data cars;
25         set sashelp.cars;
26         run;

NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set WORK.CARS has 428 observations and 15 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

27         
28         %let targlib=WORK;
29         %let targdata=CARS;
30         %let pref=pref_;
31         
32         data _null_;
33         set sashelp.vcolumn (where=(libname = "&targlib." and memname = "&targdata.")) end=eof;
34         if _n_ = 1 then call execute("proc datasets library=&targlib. nolist;modify &targdata.; rename");
35         call execute(' ' !! trim(name) !! '=' !! "&pref." !! trim(name));
36         if eof then call execute('; quit');
37         run;

NOTE: There were 15 observations read from the data set SASHELP.VCOLUMN.
      WHERE (libname='WORK') and (memname='CARS');
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: CALL EXECUTE generated line.
1         + proc datasets library=WORK nolist;modify CARS;
2                                                          The SAS System                             08:30 Tuesday, August 21, 2018

1         +                                                rename
2         +  Make=pref_Make
3         +  Model=pref_Model
4         +  Type=pref_Type
5         +  Origin=pref_Origin
6         +  DriveTrain=pref_DriveTrain
7         +  MSRP=pref_MSRP
8         +  Invoice=pref_Invoice
9         +  EngineSize=pref_EngineSize
10        +  Cylinders=pref_Cylinders
11        +  Horsepower=pref_Horsepower
12        +  MPG_City=pref_MPG_City
13        +  MPG_Highway=pref_MPG_Highway
14        +  Weight=pref_Weight
15        +  Wheelbase=pref_Wheelbase
16        +  Length=pref_Length
17        + ;
NOTE: Renaming variable Make to pref_Make.
NOTE: Renaming variable Model to pref_Model.
NOTE: Renaming variable Type to pref_Type.
NOTE: Renaming variable Origin to pref_Origin.
NOTE: Renaming variable DriveTrain to pref_DriveTrain.
NOTE: Renaming variable MSRP to pref_MSRP.
NOTE: Renaming variable Invoice to pref_Invoice.
NOTE: Renaming variable EngineSize to pref_EngineSize.
NOTE: Renaming variable Cylinders to pref_Cylinders.
NOTE: Renaming variable Horsepower to pref_Horsepower.
NOTE: Renaming variable MPG_City to pref_MPG_City.
NOTE: Renaming variable MPG_Highway to pref_MPG_Highway.
NOTE: Renaming variable Weight to pref_Weight.
NOTE: Renaming variable Wheelbase to pref_Wheelbase.
NOTE: Renaming variable Length to pref_Length.
17        +   quit
38         
39         GOPTIONS NOACCESSIBLE;

NOTE: MODIFY was successful for WORK.CARS.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

[ftenorio]

Thanks for your reply KurtBremser .

The rename using Proc Datasets, is a nice classical solution. But my point is to know if this could be achieved using Proc LUA (for educational purposes). It can be done using both LUA and Datasets, but I would like to have a simple LUA approach. See my example below:

data cars;
set sashelp.cars;
run;

%Let table_name='cars';
%Let libref='work';

proc lua restart;
submit "table_name=&table_name;libref=&libref";

function add_prefix(libref, sas_table, prefix)
	--Declaring local variables
	local var_list=''
	local libname=libref
	local dataset=sas_table
	local dsid = sas.open(libref..'.'..sas_table) -- open for input            

	-- Iterate over the variables in the data set
	for var in sas.vars(dsid) do
		var_list=var_list..' '..var.name..' = '..prefix..'_'..var.name
	end
	sas.close(dsid)

	--Execute the SAS code
	sas.submit[[
		proc datasets lib=@libname@ nolist;
		modify @sas_table@;
		rename @var_list@;
		run;
	]]
end --function

add_prefix(libref,table_name,"cr")

endsubmit;
run;

[Kurt_Bremser]

As with the macro language, lua is designed to handle code, not data. So you will always end up with executing Base SAS code for the desired action itself. Trying otherwise will probably end up causing lots of pain (as we can often see here from the abuses of the macro language).

[BrunoMueller]

For a pure LUA approach you would need a DATA Step function, that allows to modify the attributes of a SAS variable. Unfortunately this is not available in the DATA Step.

There is the MODVAR function in SCL, but this does not help here.

[ftenorio]

Thanks Bruno_SAS.