BookmarkSubscribeRSS Feed
AAWTomHanks
Calcite | Level 5

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.

6 REPLIES 6
snoopy369
Barite | Level 11

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.

AAWTomHanks
Calcite | Level 5

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;

Reeza
Super User

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

AAWTomHanks
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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.

Anotherdream
Quartz | Level 8


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).

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 1108 views
  • 0 likes
  • 5 in conversation