BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
R_A_G_
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

19 REPLIES 19
art297
Opal | Level 21

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

R_A_G_
Calcite | Level 5

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

art297
Opal | Level 21

Those ARE names.  How about something like?:

data want;

  set have;

  rename col1=people

    col2-col117=item1-item116;

run;

art297
Opal | Level 21

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;

R_A_G_
Calcite | Level 5

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.

R_A_G_
Calcite | Level 5

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.

art297
Opal | Level 21

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;

R_A_G_
Calcite | Level 5

it worked,

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

thank you

art297
Opal | Level 21

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;


R_A_G_
Calcite | Level 5

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

art297
Opal | Level 21

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?

R_A_G_
Calcite | Level 5

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

art297
Opal | Level 21

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

do i=1 to 116;


to


do iii=1 to 116;


and be sure to include iii in your drop list


R_A_G_
Calcite | Level 5

it worked but why?

thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 12329 views
  • 1 like
  • 4 in conversation