BookmarkSubscribeRSS Feed
Pyrite | Level 9



We are attempting to import an .xlsx file.  We had used the proc import statement

%let location= D:\;
%let filename=All.xlsx;

	DATAFILE= "'&location.&filename'" 

The file which has about 9000 rows imported fine except one field.  This one field with problems was missing the 3 nonzero observations which were 1 digit long.  The field:


 -is a 'General' type in the excel spreadsheet under 'Number'

-  in the Excel file had 2 observations missing information in row 2000 and 7000

- had all zeros in the spreadsheet except 3 observations which were listed in row 3000 and 6000 and 8000

- was imported as numeric in SAS


Finally, when closing and reopening SAS and restarting the computer didn't work, after using the Import WIzard, it worked.

Why did it work for the import wizard but not using the program and macro variables above?  Now, I'm importing everything using the Import wizard. 


Thank you!

Super User

A very common issue with any data entered in Excel, especially in the default general type fields, is that a value that looks like a number may actually be characters. There  are associated issues around custom displays for certain values.

Specific issues with specific files requires looking at the data to determine why.

Generally I look at CSV files in a text editor as Excel hides so much garbage in many ways.



Didn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.


However you may be learning that anything in the WORK library , as placed there with:


is temporary and deleted when SAS shuts down. Or if SAS crashes for some reason (macro programming anyone?) when restarted the WORK library is assigned to a different location. So you may be able to recover work from a crashed session. Maybe.



Pyrite | Level 9
Thanks. I tried viewing the link but it is brought me to a page that said the content moved.

Pyrite | Level 9

Didn't work means one field which had all zeros for its almost 10,000 observations except a few was imported as all zeros.


Is  the Excel file as Text (tab delimted) the same as copying and pasting into a text editor?

Or Is saving as a .csv foolproof?

Or is getting requesting it as text from the source the best?


Thank you.

Super User

If you want consistent results, get rid  of the crappy Excel file format and of proc import. Save to csv, and use a custom written data step to read that. Otherwise, you'll always be subject to the idiosyncrasies of Excel and the guessings of proc import.

Pyrite | Level 9
Thanks. Any guidance on how exactly to do that?

Opal | Level 21
  • In Excel, File Save as CSV
  • In SAS, modify your PROC IMPORT to use DBMS = CSV
  • SAS will generate DATA step code which you can see in the SAS log
  • Copy the SAS log code into your SAS editor and change it until you get your desired result
Pyrite | Level 9

Question:  How do you copy the SAS Code from the log while omitting the line numbers in the log? Or do I post as a different question?


Thank you.

Super User

@jcis7 wrote:

Question:  How do you copy the SAS Code from the log while omitting the line numbers in the log? Or do I post as a different question?


Thank you.

While marking text for copying with the mouse, hold down the Alt key. This lets you select a vertically and horizontally aligned block.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4 in conversation