Using PROC IMPORT and define field names and variable types

[data_null__]

You are saying TAB '09'x is delimiter that is wrong you have CSV.  Just remove DLM option as DSD implies DLM=','

[odoyle81]

Yay, this works!  After changing to windows style line endings (CRLF)

Thanks so much!

[Tom]

Also add to the INFILE statement these options:

FIRSTOBS=2  

TERMSTR=CRLF

Those will skip the header line with the variable names and cause the Carriage Return ('OD'x) that you see at the end of your lines to be remove.  Since SAS is running under Unix the default end of line is the bare LF ('0A'x) but since your file was created on Windows that used CRLF ('0D0A'x) as end of line the carriage return is getting included into the value of the last variable on the line.

[data_null__]

I not sure but if the line was from windows CRLF then shouldn't we see 0D at the end but there is another character after 0D 2.  Perhaps this file was created on a MAC and TERMSTR should be CR.

[odoyle81]

Yes, I'm using a mac.. I just changed line endings in the file to CRLF

[Tom]

I thought Mac's switched to LF for end of line when the OS became a Unix variant?

Anyway you can use TERMSTR=CR to read the file if it just as CR as the end of line.  But match the INFILE option to the file contents.

[gergely_batho]

I think, the reason, why your old code is not working, because it uses Windows DDE technology. SAS on Demand runs on UNIX.

Your PROC IMPORT code is OK, there's nothing you can do to improve it. Probably it is the best code to import Excel.

But you will need an additional step, that renames the columns, and maybe changes (converts) their type.

I would use the following code:

data work.RESULT;

     set WORK.rawdata;

     rename oldVarName1=newVarName1;

     rename oldVarName2=newVarName2;

     rename 'old%  nonconform Name $#'n=newVarNam3;  /*Here the old variable name contains special characters. You need to put apostrophes around it, and an n immediately after it*/

     newCharName4=put(oldNumName4, $10.);  /*This is a num2char conversion. Using the $10. format, so the new character column length will be 10*/

     newNumName5=input(oldCharName5, 10.) /*This is a char2num conversion. Using the 10. informat, so it can read 10 digits only.*/

run;

[data_null__]

In SAS categorical variables are specified with the CLASS statement or in PROC FREQ in TABLES statement; and can be either numeric or character.

I would do as suggested and save the EXCEL as tab delimited and write data step that will to read the fields using the attributes you choose.

You are going to have to learn a little SAS whether you want to or not and complaining about how it ain't R or whatever you claim to know won't help.

[jwillis]

I am curious about your need to use Proc Import.  Had you considered reading the EXCEL spreadsheet using the LIBNAME excel engine?   16176 - How to specify an Excel worksheet name using the LIBNAME Excel enginestatement

[gergely_batho]

OP uses SAS on demand, which runs on UNIX. Excel libname engine does not work there.