DATA Step, Macro, Functions and more

importing excel to sas

Reply
Frequent Contributor
Posts: 102

importing excel to sas

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

Trusted Advisor
Posts: 2,116

Re: importing excel to sas

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;

Frequent Contributor
Posts: 102

Re: importing excel to sas

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

Trusted Advisor
Posts: 2,116

Re: importing excel to sas

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.

Frequent Contributor
Posts: 102

Re: importing excel to sas

Doc,

Your code works! Sorry about my misunderstanding .

Thanks,

Lan

Occasional Contributor
Posts: 15

Re: importing excel to sas

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.

Frequent Contributor
Posts: 102

Re: importing excel to sas

Posted in reply to ChrisSelley

Thanks, Chris!

Ask a Question
Discussion stats
  • 6 replies
  • 341 views
  • 0 likes
  • 3 in conversation