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;
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;
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;
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
@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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.