Help using Base SAS procedures

import data and naming the variable

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

import data and naming the variable

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;


Accepted Solutions
Solution
‎10-05-2012 03:52 PM
PROC Star
Posts: 7,364

Re: import data and naming the variable

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


All Replies
PROC Star
Posts: 7,364

Re: import data and naming the variable

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

Regular Contributor
Posts: 161

Re: import data and naming the variable

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

PROC Star
Posts: 7,364

Re: import data and naming the variable

Those ARE names.  How about something like?:

data want;

  set have;

  rename col1=people

    col2-col117=item1-item116;

run;

PROC Star
Posts: 7,364

Re: import data and naming the variable

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;

Regular Contributor
Posts: 161

Re: import data and naming the variable

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.

Regular Contributor
Posts: 161

Re: import data and naming the variable

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.

Solution
‎10-05-2012 03:52 PM
PROC Star
Posts: 7,364

Re: import data and naming the variable

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;

Regular Contributor
Posts: 161

Re: import data and naming the variable

it worked,

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

thank you

PROC Star
Posts: 7,364

Re: import data and naming the variable

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

data want (keep=people itemSmiley Happy;

  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;


Regular Contributor
Posts: 161

Re: import data and naming the variable

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

CODE:

data want (keep=people itemSmiley Happy;

  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

PROC Star
Posts: 7,364

Re: import data and naming the variable

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?

Regular Contributor
Posts: 161

Re: import data and naming the variable

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

PROC Star
Posts: 7,364

Re: import data and naming the variable

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


Regular Contributor
Posts: 161

Re: import data and naming the variable

it worked but why?

thanks

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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