Please help - converting text date to date

[deleted_user]

Here is a sample output:

Obs char_var date_var
90 10/9/2002 10/09/2002
91 10/9/2002 10/09/2002
92 10/9/2002 10/09/2002
93 10/10/2002 .
94 10/10/2002 .
126 10/30/2002 .
127 10/31/2002 .
128 11/1/2002 11/01/2002
129 11/2/2002 11/02/2002

The . did not line up right. But they represent missing values. I do not understand this. If they are reading in the same variable how come they can read in only partial of it?

Message was edited by: cosmid

Message was edited by: cosmid

Message was edited by: cosmid Message was edited by: cosmid

[sbb]

Your best opportunity is to add the following SAS statement to your DATA step (after all assignment statements) for diagnostic and self-initiated desk-checking:

PUTLOG _ALL_;


Also, sharing the SAS generated log with your DATA step code into a post-reply will get the most distance from others' helping debug your program and input data.

Scott Barry
SBBWorks, Inc.

[Bill]

cosmid;

Might some of the dates be invalid thus generating a missing value, e.g. month value > 12, day value >28/29/30/31 in their respective months? SAS does that error checking. I'd be surprised if xl did.

[deleted_user]

All dates are valid. It was the date option I used earlier that wasn't working and once I changed it to anydtdte10. it worked.

[deleted_user]

Thanks for the tip. I never used PUTLOG _ALL_; before.

[deleted_user]

I think I know why some of the values ended up missing. Because of the default length of the variable and the data weren't read completely and that's why they ended up in missing.

how do i delete posts that i have posted?

[deleted_user]

Cosmid,

Did you do the import in SAS/EG?
Or, are you using PC SAS?
Or, are you running regular batch SAS?

It makes a difference in what the product "remembers" with reruns of stuff.

It is possible that you need to use numerical_date = input(trim(left(text_date)),mmddyy10.);

You should be able to define in the proc import the informat for the column in the excel file you are importing.

If you are using SAS/EG, you may need to delete the old import and create a new one to be able to get the filed lengths and informats set properly and remembered properly. Import in SAS/EG is a bit flaky at times.

[deleted_user]

I am using PC SAS. I am not sure what you mean when you say the product remembers with reruns of stuff. But sometimes when I restart SAS the program will start working.

I didn't use trim function in the code. I used anydtdte10. instead of mmddyy format and it started working. I also have to copy and paste the entire column of text dates from Excel to SAS in a raw format and read them in using an input statement. Everything is converted correctly that way. Using the wizard itself doesn't work.

You said I should be able to define the proc import informat for the column exel file, you mean to add a format such as mmddyy after the column's variable right?

[deleted_user]

PC SAS and SAS/EG both open active SAS Sessions, but EG is actually a .NET application that caches project information and generated code, which is stored in the project file, unless you explicitly tell it to store the code in a separte .sas file.

Now, since PC SAS starts a local SAS session, as you work on stuff, stuff is kept in the SAS registry and in the SAS "WORK" library. Also, compiled code is kept in memory. This can cause what would appear to be strange behavior if you make changes to what you are doing. Some of the "remembered" stuff held in memory doesn't necessarily get updated.

With "import" with SAS EG, some things just can't get updated the way you want since you can't directly update the generated code. So, I have had to delete an "import", stop the EG session, and then start it up again and recreate the import to be able to make some changes.

I just looked at the documentation for Proc Import, that is not helpful.

You should be able to write a single data step with infile and input statements that will let you read in the Excel sheet directly without extra manual processing. You may need to use the ATTRIB statement in front of the infile and input statements to predefine the variables, their informats and formats, to help.

I've inherited code sets that make use of Excel spreadsheets, and know that they can be a pain. One of the spreadsheets of initial rows of header information that had to be manually deleted to insure the actual columnar information began in row 1 so that SAS would read it properly.

[deleted_user]

Thanks for the explanation! I know about the work library and how SAS keep some stuff in registry. But I wasn't aware that not all data in memory are updated. I will be more cautious in the future.

Now I am going to run some tests on ATTRIB statement. I never used this before.

Thanks again for the help!

[deleted_user]

One more thing about Excel and dates... Any date in Excel prior to 1/1/1900 is stored in Excel as characters, so even if you do a PROC IMPORT and it seems to work, any pre-1900 date will be set to missing.

[deleted_user]

Thanks for the tip. Hopefully I won't work with date that early.