Dynamically change the name of variables

[A_Swoosh]

Hello,

I've looked through these forums and examples to potentially dynamically change the name of my variables in my datasets but to no avail. 

Problem: I want to change the names of my variables in each of the 16 datasets that I'm feeding into SAS. Each dataset may or may not have all the same variables (i.e., some datasets may have more variables than others). I'm looking for a way to dynamically change variable names through each iteration of my SAS datasets only looking at the variables in those datasets.

Solution (?):

1. Proc sql to create variable list, then use an array and do loop to iterate through each dataset? 
2. Read an excel file into sas and use rename?
3. use a %let and rename
4. a macro

Have: 

Dataset 1

ProvID NPI Txnmy_cd1 Txnmy_cd2 Txnmy_cd3 Spec_cd1 Spec_cd2

Dataset 2

ProvID NPI txnmy_cd txnmy_cd2 Spec_cd Spec_cd1

and so on...

Want:

Dataset 1

ProvID NPI Taxonomy Taxonomy2 Taxonomy3 ProvSpec1 ProvSpec2 

Dataset 2

ProvID NPI Taxonomy Taxonomy2 Taxonomy3 ProvSpec1 ProvSpec2 

 Any suggestions would be helpful...Thanks!

[Shmuel]

From your example I understand that you want to change prefixes of variable names.

You cam use sql dictionary.columns or sashelp.vcolumn to get variable names of a dataset,

create the rename statement as a macro variable to be used with proc datasets and rename 

variables without copying the dataset:

Next code is tesed:

data have;
     ProvID=1;
     Txnmy_cd1=21;
     Txnmy_cd2=22;
     Txnmy_cd3=23;
     Spec_cd1 =31;
     Spec_cd2 =32;
run;

%let lib = WORK;
%let dsn = HAVE;
data _null_;
 set sashelp.vcolumn end=eof;
     where libname = "&lib" and memname = "&dsn";
	 length new_name $32 rename $1000;
	 retain rename;
	 if substr(name,1,6) = 'Txnmy_' 
	    then new_name = 'TAXONOMY' || substr(name,7); else
	 if substr(name,1,7) = 'Spec_cd'
	    then new_name = 'PROVSPEC' || substr(name,8); 
	 else new_name = ' ';
     if not missing(new_name) then		
	 rename = compbl(rename) || ' ' || strip(name) || '=' || strip(new_name);
	 if eof then call symput('RENAME', strip(rename));
run;
%put &rename;   /* to be checked in the log */

proc datasets lib=&lib;
      modify &dsn;
	  rename &rename;
run;

[A_Swoosh]

Hi Shmeul,

Thank you for the response. Yes, I want to essentially create prefixes and suffix for a newly standardized list of variables. To follow up, I did have a question for you code; is there a way to automate this process without creating these conditional statements and substrings? The list I provided was a small subset of the variables that I want to either prefix/suffix so I would then essentially have to generate conditional statements for each variable prefix/suffix I use unless there is some way to further automate this process? So for example, I have city, state, address,zip information but want to change those with a prefix of "Prov" too.

[Patrick · Accepted solution]

Here some code which should give you a lot of flexibility. Just expand informat $varname_stdz to whatever renaming you need and add the tables in scope to the data _null_ step at the end.

You could also pass in different informat names should you need it.

data have1;
  array vars {*} ProvID NPI Txnmy_cd1 Txnmy_cd2;
  stop;
run;

data have2;
  array vars {*} ProvID NPI txnmy_cd;
  stop;
run;


%macro renameVars(tbl,infmt_stdz);
  %local lib;
  %let lib=%upcase(%scan(WORK.&tbl,-2,.));
  %let tbl=%upcase(%scan(&tbl,-1,.));

  %local rename_vars;
  %let rename_vars=;
  proc sql noprint;
    select cats(name,'=',input(name,$&infmt_stdz.32.)) 
      into :rename_vars separated by ' '
    from dictionary.columns
    where 
      libname="&lib" 
      and memname="&tbl"
      and not missing(input(name,$&infmt_stdz.32.))
    ;
  quit;
  
  %if %nrbquote(&rename_vars) ne %nrbquote() %then
    %do;
      proc datasets lib=&lib nolist;
        modify &tbl;
          rename &rename_vars;
        run;
      quit;
    %end;
%mend;

proc format;
  invalue $varname_stdz(default=32 upcase)
    'TXNMY_CD'  = 'Taxonomy'
    'TXNMY_CD1' = 'Taxonomy'
    'TXNMY_CD2' = 'Taxonomy2'
    other=' '
    ;
run;

data _null_;
  infile datalines dsd dlm=' ' truncover;
  input tbls :$41. infmt_stdz :$32.;
  infmt_stdz=coalescec(infmt_stdz,'varname_stdz');
  rc=dosubl(cats('%renameVars(',tbls,',',infmt_stdz,');'));
  datalines;
have1
work.have2
haveNot
;

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: Renaming variable Txnmy_cd1 to Taxonomy.
NOTE: Renaming variable Txnmy_cd2 to Taxonomy2.
NOTE: MODIFY was successful for WORK.HAVE1.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: Renaming variable txnmy_cd to Taxonomy.
NOTE: MODIFY was successful for WORK.HAVE2.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

[A_Swoosh]

Hi Patrick,

Thank you for the response. I'm a little lost with your code. 

At the top, you have:

data have1;
  array vars {*} ProvID NPI Txnmy_cd1 Txnmy_cd2;
  stop;
run;

data have2;
  array vars {*} ProvID NPI txnmy_cd;
  stop;
run;

If I'm automating through each dataset, what does this accomplish?

Second, I'm not sure I follow this end piece here specifically the have1 work.have2 havenot;:

data _null_;
  infile datalines dsd dlm=' ' truncover;
  input tbls :$41. infmt_stdz :$32.;
  infmt_stdz=coalescec(infmt_stdz,'varname_stdz');
  rc=dosubl(cats('%renameVars(',tbls,',',infmt_stdz,');'));
  datalines;
have1
work.have2
haveNot
;

I have written a piece of code to iterate through each of my datasets. I used this to call out specific things that I want to use throughout.

	        data _NULL_  ;
	                set sasfiles;
	                if _n_ = &i.;
	                call symputx('name',SAS_name);
					call symputx('plan',substr(SAS_name,5,3));
	        run;
				%put "&name.";
				%put "&plan.";

I then formatted my code slightly to modify a few elements here:

			data &name. (drop=start_date end_date);
				set outdta.&name.;

				/* Create plan name */
				Plan="&plan.";
/*				Plan_LName=put(plan, $Plan_Abbrev.);*/

/*				/* Format date variables */*/;
				StartDate=input(start_date, yymmdd10.);
				EndDate=input(end_date, yymmdd10.);

				format StartDate EndDate mmddyy10. plan $4.;
			run;

I then want to use your formatting suggestion to rename my variables using a longer list than the one you did (for example) which I added to my infmt_stdz format.

My end result is that I want to automate this as much as possible and let the data drive my code so from one year to the next, I can simply change the format you mentioned and let it ride...

[Patrick]

Top

That's just creating sample tables have1 and have2 so I've got something to work with.

Second

That code is calling the macro via dosubl() once per table. The macro then renames the variables; and the macro is written in a way that if you pass in a not existing table name - haveNot in the sample code - then just nothing will happen but the macro doesn't fail.

You can replace this data step with whatever suits you. You just need the dosubl() bit:

rc=dosubl(cats('%renameVars(',tbls,',',infmt_stdz,');'));

tbls: You can replace this with the variable that has the name of the table you where you want to rename variables. If the values of the variable only have the table name then the macro will look for a table in WORK; else pass in a name with the libref like mylib.mytable

infmt_stdz: This variable has the name of the Informat you want to use (name without a dot). You could also just hard-code that when calling the macro, i.e. if your informat has a name like $varname_stdz then below would work.

rc=dosubl(cats('%renameVars(',tbls,',varname_stdz);'));

[A_Swoosh]

/* Listed Separately */
proc format; 
  invalue $varname_stdz(default=32 upcase)
  	'Prov_type' = 'ProvType'
	'Prov_typeB' = 'ProvType'
    'TXNMY_CD'  = 'Taxonomy'
    'TXNMY_CD1' = 'Taxonomy'
    'SPEC_CD' = 'ProvSpec1'
    'SPEC_CD1' = 'ProvSpec1'
    other = ' '
;
run;

%macro loopy;
	proc sql noprint;
		select max(monotonic()) into :completion from sasfiles;
		%let completion=&completion;
	quit;
	%put &completion.;

	        %let i = 1;
	        %do %while( &i.<= &completion.);

	        data _NULL_  ;
	                set sasfiles;
	                if _n_ = &i.;
	                call symputx('name',SAS_name);
					call symputx('plan',substr(SAS_name,5,3));
	        run;
				%put "&name.";
				%put "&plan.";


%macro renameVars(tbl,infmt_stdz);
  %local lib;
  %let lib=%upcase(%scan(WORK.&name.,-2,.));
  %let tbl=%upcase(%scan(&tbl,-1,.));

  %local rename_vars;
  %let rename_vars=;
  proc sql noprint;
    select cats(name,'=',input(name,$&infmt_stdz.32.)) 
      into :rename_vars separated by ' '
    from dictionary.columns
    where 
      libname="&lib" 
      and memname="&tbl"
      and not missing(input(name,$&infmt_stdz.32.))
    ;
  quit;
  
  %if %nrbquote(&rename_vars) ne %nrbquote() %then
    %do;
      proc datasets lib=&lib nolist;
        modify &tbl;
          rename &rename_vars;
        run;
      quit;
    %end;
%mend;

			data &name. (drop=start_date end_date);
				set outdta.&name.;

				/* Create plan name */
				Plan="&plan.";
/*				Plan_LName=put(plan, $Plan_Abbrev.);*/

/*				/* Format date variables */*/;
				StartDate=input(start_date, yymmdd10.);
				EndDate=input(end_date, yymmdd10.);

				format StartDate EndDate mmddyy10. plan $4.;

			   rc=dosubl(cats('%renameVars(',&name.,',',varname_stdz,');'));

			run;

Ok, so here's what I have so far but it's not working. I'm not sure where I'm messing up. The result is an error message: "syntax error, expecting one of the following: ), -.. The symbol is not recognized and will be ignored. 

[Patrick]

Looking at your code I find it a bit hard to understand what you're trying to do so here just a few remarks.

1. You've got two %macro statements but only a single %mend 

2. You seem to misunderstand how the macro I've posted works. You only call the macro ONCE per table. A rename operates against the descriptor portion ("the header") of a table and not against the data portion.

Can you please share how table "SASFILES" looks like? May be post a print of a few rows.

[A_Swoosh]

SASfiles is my way to call all my datasets; it looks like this:

Filename
Data1
Data2
Data3
Data4
...
Data16

2. Yea, I don't think I'm following that aspect of it.

[Patrick]

---

@A_Swoosh wrote:

SASfiles is my way to call all my datasets; it looks like this:

Filename
Data1
Data2
Data3
Data4
...
Data16

2. Yea, I don't think I'm following that aspect of it.

---

To call the macro for a specific table in work would look like:

%renameVars(work.mytable,varname_stdz);

If you've got a SAS table with the table names (your table "sasfiles") then here how you can call the macro once per observation.

data _null_;
  set sasfiles;
  rc=dosubl( cats('%renameVars(',filename,',varname_stdz);') );
run;

N.B: Make sure that the value of your variable Filename contains <libref>.<table name>. If you omit the libref then the macro will look in WORK.