topic Re: Import Excel and Make a Column with Dates a Character Column in SAS Procedures

Import Excel and Make a Column with Dates a Character Column

AAWTomHanks — Mon, 21 Oct 2013 19:54:02 GMT

Hi,

I am bringing in a table from excel to SAS. I am having trouble with converting one of the columns into a character column. I have a column in excel with some of the cells as dates and some of the cells as text. I would like to import this column as text though. How can I do this?

Thanks.

Re: Import Excel and Make a Column with Dates a Character Column

snoopy369 — Mon, 21 Oct 2013 20:03:53 GMT

It somewhat depends on how you're importing it (which DBMS you're using), but most of the time the MIXED=YES option is helpful for what you're asking. If not, post your full import code (with filename anonymized if needed) so you can get the best help possible.

Re: Import Excel and Make a Column with Dates a Character Column

AAWTomHanks — Mon, 21 Oct 2013 20:11:00 GMT

PROC IMPORT OUT= WORK.IMPRT_EXCEL DATAFILE= 'C:/new.xls' DBMS=EXCELCS REPLACE; PORT=0000; SERVER='01.01.001.01'; SHEET="grp"; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;

Re: Import Excel and Make a Column with Dates a Character Column

Reeza — Mon, 21 Oct 2013 20:26:26 GMT

You said some date, some text.
What does that look like in Excel?

Re: Import Excel and Make a Column with Dates a Character Column

AAWTomHanks — Mon, 21 Oct 2013 20:39:16 GMT

The column has dates like this 8/12/2012 and text like this "not signed". Some of the cells are blank too.

Re: Import Excel and Make a Column with Dates a Character Column

Linlin — Mon, 21 Oct 2013 20:48:19 GMT

I could add three rows with text value at the top of your excel file to force sas treating your date column as text and delete the added row after importing.

Re: Import Excel and Make a Column with Dates a Character Column

Anotherdream — Mon, 21 Oct 2013 22:38:39 GMT

You could also specify the value "guessingrows=32000". That will make SAS look at the first 32,000 rows of data to guess the type instead of the first 8.

If you can't be guaranteed that the text occurs in the first 32000 rows, then I would suggest not using proc import from an excel file (frankly I suggest this anyways. Excel is just an awful data storage program).