topic Re: import data and naming the variable in SAS Procedures

import data and naming the variable

R_A_G_ — Fri, 05 Oct 2012 13:41:23 GMT

Hello,

I have a data set that I would like to import and name the variables.

This code works but don't know how to name them

thank you

Proc import file="C:\try.xls" out=data dbm=xls replace;

startrow=1;

getnames=no;

run;

Re: import data and naming the variable

art297 — Fri, 05 Oct 2012 14:16:05 GMT

You could follow your import with a datastep that uses either the rename option (in the set statement) or the rename statement.

Re: import data and naming the variable

R_A_G_ — Fri, 05 Oct 2012 14:24:37 GMT

But I don't have any names for my variables to rename.

the first variable has to be named people and the rest item1-item116

thanks

Re: import data and naming the variable

art297 — Fri, 05 Oct 2012 14:51:41 GMT

Those ARE names. How about something like?:

data want;

set have;

rename col1=people

col2-col117=item1-item116;

run;

Re: import data and naming the variable

art297 — Fri, 05 Oct 2012 15:01:01 GMT

I just noticed that you used the xls engine. That produces names like A, B, etc.

If you use the Excel engine instead, you will get names like F1-F117. Then you could use:

Proc import file="C:\try.xls" out=data dbms=excel replace;

getnames=no;

run;

data want;

set data;

rename f1=people

f2-f117=item1-item116;

run;

Re: import data and naming the variable

R_A_G_ — Fri, 05 Oct 2012 17:10:07 GMT

I tried the last code code but for some reason it's giving me this error:

2104 Proc import file="C:\Users\Roofia\Desktop\DATA\try.xls" out=IEEE.data dbms=excel replace;

ERROR: DBMS type EXCEL not valid for import.

Re: import data and naming the variable

R_A_G_ — Fri, 05 Oct 2012 17:21:07 GMT

Also this one does not work.

data new;

set data;

rename col1=CE

col2-col117=item1-item116;

run;

Error:

WARNING: The variable col1 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: Not all variable names in the RENAME list col2-col117 were used.

NOTE: There were 96 observations read from the data set DATA.

Re: import data and naming the variable

art297 — Fri, 05 Oct 2012 19:52:23 GMT

Don't know why you can't use excel engine, but there are always alternatives. How about?:

Proc import file="C:\try.xls" out=data dbms=xls replace;

getnames=no;

run;

data want (drop=b--dq);

set data;

array data_in(*) b--dq;

array item(116);

rename a=people;

do i=1 to 116;

item(i)=data_in(i);

end;

run;

Re: import data and naming the variable

R_A_G_ — Fri, 05 Oct 2012 20:28:20 GMT

it worked,

except that I had to rename and label it to people as well.

thank you

Re: import data and naming the variable

art297 — Fri, 05 Oct 2012 21:19:53 GMT

Ah, the label(s). You could have accomplished both, I think, with just:

data want (keep=people item:);

set data;

array data_in(*) a--dq;

array item(116);

people=data_in(1);;

do i=1 to 116;

item(i)=data_in(i-1);

end;

run;

Re: import data and naming the variable

Tom — Fri, 05 Oct 2012 23:59:06 GMT

Why not just skip the PROC IMPORT? Save the XLS files as a CSV file and then just read it with a data step where you get to define the names?

data want ;

infile 'myfile.csv' dsd dlm=',' truncover lrecl=30000;

input people col1-col116;

run;

Re: import data and naming the variable

R_A_G_ — Sat, 06 Oct 2012 18:38:19 GMT

I cannot figure out why but this new code does not work

CODE:

data want (keep=people item:);

set data;

array data_in(*) a--dq;

array item(116);

people=data_in(1);;

do i=1 to 116;

item(i)=data_in(i-1);

end;

run;

ERROR: All variables in array list must be the same type, i.e., all numeric or character.

ERROR: All variables in array list must be the same type, i.e., all numeric or character

Instead I just added this( rename A=people; label A=people) did this and worked except...:

But I have another problem on my hand now.

after renaming the data I've noticed that one of the variables' value,"item 8 " is recorded as "8" not its real value. why is that?

thanks

Re: import data and naming the variable

art297 — Sat, 06 Oct 2012 18:53:08 GMT

My latest suggestion didn't work because, apparently, imported variable A is character, while the others are either numeric or mixed. What code did you use that is giving you your latest incorrect value and what did your log show?

Re: import data and naming the variable

R_A_G_ — Sat, 06 Oct 2012 19:05:53 GMT

thanks for letting me know about the last problem.

my code is:

data new (drop=b--dm);

set old;

rename A=people;

label A=people;

array data_in(*) b--dm;

array item(116);

do i=1 to 116;

item(i)=data_in(i);

end;

run;

as for the log there is no sign of problems

LOG:

140 data new (drop=b--dm);

141

142 set old;

143

144 rename A=people;

145

146 label A=people;

147

148 array data_in(*) b--dm;

149

150 array item(116);

151

152 do i=1 to 116;

153

154 item(i)=data_in(i);

155

156 end;

157

158 run;

NOTE: There were 96 observations read from the data set WORK.OLD.

NOTE: The data set WORK.NEW has 96 observations and 117 variables.

NOTE: DATA statement used (Total process time):

real time 0.04 seconds

cpu time 0.04 seconds

Re: import data and naming the variable

art297 — Sat, 06 Oct 2012 19:12:01 GMT

You have two variables labled "i". Change your loop from

do i=1 to 116;

do iii=1 to 116;

and be sure to include iii in your drop list

Re: import data and naming the variable

R_A_G_ — Sat, 06 Oct 2012 19:29:36 GMT

it worked but why?

thanks

Re: import data and naming the variable

art297 — Sat, 06 Oct 2012 19:35:08 GMT

One of your variables was called "i" and I, without thinking, had suggested using "i" in the do loop. As such, the loop was re-assigning the value of that variable (i.e., the eighth variable).

Re: import data and naming the variable

R_A_G_ — Sat, 06 Oct 2012 21:16:28 GMT

Just curious to know why iii fixed the problem

thanks

Re: import data and naming the variable

R_A_G_ — Sat, 06 Oct 2012 21:23:50 GMT

thanks didn't see the reply, please ignore the last question

Re: import data and naming the variable

Ksharp — Mon, 08 Oct 2012 01:58:35 GMT

Or a faster way.

Proc import out=data datafile="C:\temp\x.xls"  dbms=xls replace;
  getnames=no;
run;

data _null_;
 set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='DATA')) end=last ;
 if _n_ eq 1 then call execute('proc datasets library=work nolist;modify data; rename a=people ');
  else call execute(cats(name,'=col',_n_-1));
 if last then call execute(';quit;');
run;

Ksharp