topic Re: File Imported Different from actual file in SAS Procedures

File Imported Different from actual file

jcis7 — Thu, 03 May 2018 22:32:20 GMT

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!

Re: File Imported Different from actual file

ballardw — Wed, 16 May 2018 15:32:47 GMT

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.

Re: File Imported Different from actual file

Kurt_Bremser — Fri, 04 May 2018 04:48:09 GMT

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.

Re: File Imported Different from actual file

jcis7 — Fri, 04 May 2018 17:40:41 GMT

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

Re: File Imported Different from actual file

jcis7 — Fri, 04 May 2018 17:41:41 GMT

Thanks. Any guidance on how exactly to do that?

Re: File Imported Different from actual file

jcis7 — Wed, 09 May 2018 18:16:53 GMT

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.

Re: File Imported Different from actual file

SASKiwi — Sun, 13 May 2018 20:38:08 GMT

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

Re: File Imported Different from actual file

jcis7 — Tue, 19 Jun 2018 21:32:54 GMT

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.

Re: File Imported Different from actual file

Kurt_Bremser — Wed, 20 Jun 2018 05:10:19 GMT

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