BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

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!

8 REPLIES 8
ballardw
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: 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
Pyrite | Level 9
Thanks. I tried viewing the link but it is brought me to a page that said the content moved.


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

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

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


SASKiwi
PROC Star
  • 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
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.

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

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
  • 8 replies
  • 990 views
  • 4 likes
  • 4 in conversation