IMPORT FROM EXCEL WITH COLUMN RENAMING 1

[RinaGreen]

Hi

I need to import file from Excel to SAS and to rename all fields. Actually I already imported file from Access ones.

I assume it should be like the following....I assume I just need to put FILE instead TABLE (in Access). Also I assume I need

to specify a sheet....

I am not sure how to rename columns names in Excel in order tem to be consistent in SAS...I tried to use help on-line . Below

is what I assume...

Also...some of my member id in excel do not have leading zeroes which I need if member id is less then 9 digits (for ex I

need 012345678 instead 12345678). I assume I need to use FORMAT function? In what way?


Thank you very much in advance

Rina
*******************************************************************************

libname mylib 'c:\myDir\rina\myFolder\SAS Datasets';

*** InTbl: Name of Sheet in Excel worksheet to be imported;
*** SASTbl: Name of SAS output data set;
*** InFl: Complete path and name (with .xls extension) of Excel file to be imported;

proc import file='sheet1'
out=myOutput
dbms=excel;
workgpdb='myGrp';

RENAME MEMBER_ID = MEMBER ID
NUMBERS_OF_VISITS=# OF VISITS
MEMBER_REFFERED=HAS MEMBER BEEN REFFERED;


file='c:\myDir\rina\myFolder\myFile 'v2000.xls';
userid='rina';
password=' ';
workgpdb='myGrp';
run;

 

[mplee]

in my experience you can't rename the columns when importing from excel. that would be easy enough to do in a subsequent data, step, however, which is what i usually do. btw, the names you want are illegal since they have embedded spaces. you can use the label command, however, to change how they are displayed.

as for the leading zeros, it depends on how you want to treat the number and how it's formatted in the original data. the problem i frequently encounter when importing is that SAS makes its best guess on whether an Excel column is a numeric or character based on first few rows. if your data is formatted in Excel as text with leading zeros then SAS may improperly import it as numeric, thus dropping the leading zeros.

personally, i prefer to keep id numbers as characters. i only use numerics for fields that i plan on doing some arithmetic with.

if you want to treat id as a numeric with leading zeros i would use the following code.

proc import file="\path\excel workbook.xls"
        out=myOutput
        dbms=excel;
        workgpdb='myGrp';   
        sheet='sheet1';
        run;

data myOutput;
        set myOutput;
        label  MEMBER_ID = 'MEMBER ID'
               NUMBERS_OF_VISITS='# OF VISITS'
               MEMBER_REFFERED='HAS MEMBER BEEN REFFERED';
        format member_id z9.;
        run;

the "z9." format will pad a number with leading zeros so that the total length is 9 digits.


if you want to convert the member_id to a numeric then i would use the following data step.

data myOutput;
        format member_id $9.;
        set myOutput(rename=(member_id=member_idn));
        member_id=put(member_idn,z9.);
        label  MEMBER_ID = 'MEMBER ID'
               NUMBERS_OF_VISITS='# OF VISITS'
               MEMBER_REFFERED='HAS MEMBER BEEN REFFERED';
        drop member_idn;
        run;

putting the format statement before the set statment means that the member_id variable will be the first listed, which i do as a matter of convenience. the set statement uses the rename option to change the name of the numeric variable so that member_id can be used for the new character variable. then member_id is set as equal to member_idn (the old numeric version) using the z9. function, which left pads with zeros. finally you drop the old numeric version.

i hope this helps.

good luck!

** mp **

 

[ChrisW75]

You can also rename a column using Proc Datasets, which has the advantage that it does not read the dataset through, just renames the column, so in a large dataset this is much faster than using a data step. In this case though, the number of records will be less than 65536 rows as that is the maximum allowed in Excel.
Are you saying that you exported your data from Access to Excel, and are now importing it from Excel to SAS? If so, I would suggest having a look at this thread which shows how to import direct from Access to SAS.

http://www.tek-tips.com/viewthread.cfm?qid=1022964&page=1

It is always a good idea to do as few manual steps as possible, and definitely as few import/exports as possible. Personally, I avoid using Excel altogether as it frequently decides to "fix" data formats, specifically numerics and dates.

 

[RinaGreen]

Chris,

In my previous thread I needed to import for Access to SAS.
In the current one I had needed to import from Excel to SAS.

I had to change all column headings in Excel however because SAS didn't allow me to rename from improper Excel names (like member id without underscore). On the other han, I coudn't use LABLE because of the next operations/manipulations in SAS

sinse You have just mentioned Proc Datasets... I am not sure how to use it in order to import excel file to SAS...
Could you please give me an example? Thank you in advance.

Rina

 

[ChrisW75]

Hi Rina,
Proc datasets is not used to migrate the data, only for renaming the columns, sorry to cause confusion. I was just throwing that in as an advanced method for renaming columns in a SAS Dataset without using a datastep.
MPLEEs method is the right way to go.