File Imported Different from actual file

[jcis7]

G'day.

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

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


PROC IMPORT OUT= WORK.all 
	DATAFILE= "'&location.&filename'" 
            DBMS=EXCEL REPLACE;
     RANGE="All$"; 
     GETNAMES=YES;
     MIXED=NO;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

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!

[ballardw]

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: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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:

PROC IMPORT OUT= WORK.all 

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.

[jcis7]

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

[jcis7]

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.

[Kurt_Bremser]

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.

[jcis7]

Thanks. Any guidance on how exactly to do that?

[SASKiwi]

• 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

[jcis7]

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.

[Kurt_Bremser]

---

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