topic Re: importing excel to sas in SAS Programming

importing excel to sas

LanMin — Wed, 24 Apr 2013 15:27:22 GMT

HI, all

attached is a sample of my data. when I import into sas, gvkey
variable drop the initial 00 , but I need the content intact. that is
what I expected is a sas dataset with 001001.

gvkey year index

001001 1983 1.005130

001001 1984 1.003507

001001 1985 1.004025

001003 1982 1.017350

please help! thanks!

Lan

Re: importing excel to sas

Doc_Duke — Wed, 24 Apr 2013 17:20:35 GMT

Enterprise Guide can handle that conversion for you, but if you are using PROC IMPORT you will have to post-process the data.

If gvkey is desired to be numeric, you can use PROC DATASETS to apply a format (Z6.) to it.

If gvkey is actually a character string that happens to be all digits, then you will need to use a data step to convert, something like

DATA new;

SET _LAST_(RENAME=(gvkey=gvkeyold));

LENGTH gvkey $6.;

gvkey=PUT(gvkeyold,Z6.);

DROP gvkeyold;

RUN;

Re: importing excel to sas

LanMin — Thu, 25 Apr 2013 16:13:14 GMT

Thanks, Doc!

I do not have Enterprise Guide.

gvkey is a charater, I formatted as text in excel, but when I use SAS- File-Import data (i.e. menu option), it drops the initial 00 from (001001) to 1001 (outcome), could you show some code that use proc import (or other methods) that will preserve the initial 00?

Lan

Re: importing excel to sas

LanMin — Thu, 25 Apr 2013 16:35:16 GMT

Doc,

Your code works! Sorry about my misunderstanding .

Thanks,

Lan

Re: importing excel to sas

Doc_Duke — Thu, 25 Apr 2013 17:14:27 GMT

PROC IMPORT scans the Excel records to determine the data type, it does not use the Excel format. If you don't have EGuide, the only way I know how to convert is with the DATA step mentioned earlier.

Re: importing excel to sas

ChrisSelley — Tue, 30 Apr 2013 12:38:59 GMT

You can use the LIBNAME statement to access Excel files

Like this:

LIBNAME XXX EXCEL "C:\TEST.XLS";

Then set a length/format before reading from the selected sheet

DATA TEST;

LENGTH GVKEY $6.;

SET XXX."SHEET1$"N;

RUN;

That way you read it as character.

Re: importing excel to sas

LanMin — Thu, 02 May 2013 15:40:50 GMT

Thanks, Chris!