topic Re: Rename all variables at once in SAS Programming

Rename all variables at once

mlogan — Fri, 08 Feb 2019 03:34:45 GMT

Hi there,

I am trying to import data from a csv file and the only row I want to import is the first row. Following code is working, but I am getting my variable name as Var1, Var2, Var3 etc. Is there a way to name my variables as Var0001, Var0002, Var0003 during the import. I will appreciate any help on this.

OPTIONS obs=1;
PROC IMPORT DATAFILE="C:\Test.csv" 
OUT=Want
     DBMS=csv REPLACE;
	 GETNAMES=NO;
	 DATAROW=1;
RUN;

Re: Rename all variables at once

Reeza — Fri, 08 Feb 2019 03:50:23 GMT

You can rename it in one step, but not sure you can do that in PROC IMPORT.

data class;
set sashelp.class;
rename name=var1 sex = var2 age = var3 height = var4 weight = var5;
run;

data want;
set class;
rename var1-var5 = var001-var005;
run;

proc contents data=want;
run;

Results:

Alphabetic List of Variables and Attributes
#	Variable	Type	Len
1	var001	Char	8
2	var002	Char	1
3	var003	Num	8
4	var004	Num	8
5	var005	Num	8

Note that I'm assuming you're using SAS Base and have moved the question to the programming forum. If you're using Data Flux or Data Integration Studio there may be different options and I can move this question back to the Data Management forum.

@mlogan wrote:

Hi there,

I am trying to import data from a csv file and the only row I want to import is the first row. Following code is working, but I am getting my variable name as Var1, Var2, Var3 etc. Is there a way to name my variables as Var0001, Var0002, Var0003 during the import. I will appreciate any help on this.
OPTIONS obs=1;
PROC IMPORT DATAFILE="C:\Test.csv" 
OUT=Want
     DBMS=csv REPLACE;
	 GETNAMES=NO;
	 DATAROW=1;
RUN;

Re: Rename all variables at once

ScottBass — Fri, 08 Feb 2019 04:07:25 GMT

If your source dataset had more random variable names that did not lend themselves to using variable list syntax, then here is a more generic approach:

* simulate import of data ;
data have;
   set sashelp.cars (obs=3);
run;

* create metadata ;
proc contents data=have out=contents noprint;
run;
proc sql noprint;
   select name into :vars separated by " " from contents order by varnum;
   drop table contents;
quit;

* macro to rename columns ;
%macro code;
&word=Var%sysfunc(putn(&__iter__,z3.))
%mend;

* use proc datasets to rename ;
proc datasets lib=work nolist;
   modify have;
   rename %loop(&vars);
   run;
quit;

https://github.com/scottbass/SAS/blob/master/Macro/loop.sas

Re: Rename all variables at once

RW9 — Fri, 08 Feb 2019 09:43:03 GMT

Why not drop the guessing procedure fully. Write a datastep to import your CSV, using the structure that you know best (presumably using the data import agreement you have). This way you don't let the system guess what your data is supposed to look like, can have your given data structure, and it be repeatable and QC'able. Proc import is only useful for getting the base code to work with, run it once, then copy the code generated from the log and modify it to match the data that only you know best.

Re: Rename all variables at once

Ksharp — Fri, 08 Feb 2019 14:48:24 GMT

data class;
set sashelp.class;
rename name=var1 sex = var2 age = var3 height = var4 weight = var5;
run;



proc transpose data=class(obs=0) out=temp;
var _all_;
run;
data temp;
 set temp;
 n=input(compress(_name_,,'kd'),best.);
 new_name=cats(compress(_name_,,'d'),put(n,z4.));
run;

data _null_;
 set temp end=last;
 if _n_=1 then call execute('proc datasets library=work nolist nodetails;modify class ; rename ');
 call execute(cats(_name_,'=',new_name));
if last then call execute(';quit;');
run;


proc print data=class;run;

Re: Rename all variables at once

mlogan — Fri, 08 Feb 2019 16:04:47 GMT

Hi ScottBass,
Your code is giving me an error. Would you please find the error. I am getting the following error. Thanks.

164 PROC DATASETS LIB=work NOLIST;
165 MODIFY have;
WARNING: Apparent invocation of macro LOOP not resolved.
166 RENAME %loop(&vars);
-
22
76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
167 RUN;

NOTE: Statements not processed because of errors noted above.
168 QUIT;

Re: Rename all variables at once

ScottBass — Mon, 11 Feb 2019 05:26:27 GMT

@mlogan Click the github link, save the macro, and either compile it or save it in your sasautos path. You may also need to download other github macros such as %parmv.