BookmarkSubscribeRSS Feed
LanMin
Fluorite | Level 6

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

6 REPLIES 6
Doc_Duke
Rhodochrosite | Level 12

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;

LanMin
Fluorite | Level 6

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

Doc_Duke
Rhodochrosite | Level 12

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.

LanMin
Fluorite | Level 6

Doc,

Your code works! Sorry about my misunderstanding .

Thanks,

Lan

ChrisSelley
Calcite | Level 5

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.

LanMin
Fluorite | Level 6

Thanks, Chris!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 1240 views
  • 0 likes
  • 3 in conversation