Help using Base SAS procedures

importing data from excel - some data points not importing

Reply
Occasional Contributor
Posts: 10

importing data from excel - some data points not importing

I am importing a spreadsheet with a few thousand rows of data and two columns

All of the first field are importing properly but some of the second field are not.

For example, this is what the import file would look like

SKU_NUMBER              PARENT_SKU

123                                  400

124                                  411        

125                                  414

130                                  418

but this is what might import

SKU_NUMBER              PARENT_SKU

123                                  400

124                                  .      

125                                  .

130                                  418

I can't figure out why some of the data points are getting missed.

I am assuming it's a formatting issue but all of the cells seem to have the same number format. (though it's weird, they appear slightly different -the ones not getting imported are sligthly indented on the right... but i can't find anywhere in excel that can tell me the difference in formatting - number format, alignement, font, etc all seems to be the same)

any help would be much appreciated!  (tried to upload a sample file but not excel allowed!)

Regular Contributor
Posts: 168

Re: importing data from excel - some data points not importing

How did you import? e.g. proc import, infile statement.

Provide your code as well.

Occasional Contributor
Posts: 10

Re: importing data from excel - some data points not importing

I didn't use code to import that data.  I used the "import data" wizard from the SAS Enterprise menu.

both are number fields.

i thought both with BEST12 but looks like the first is F12 and the second is BEST12  (not sure what the difference is)

Super Contributor
Posts: 297

Re: importing data from excel - some data points not importing

You are saying that that the data points not being imported are indented.

Is there a possiblity that some of these are actually character values?

Do a check using the excel function ISNUMBER.  The Syntax is ISNUMBER(CELL REF).

Occasional Contributor
Posts: 10

Re: importing data from excel - some data points not importing

smart man.  they are not nujmbers - and there is a trailing space in these cells.  (thanks didn't know the isnumber formula)

Now, barring going into each cell and fixing them, how can i fix this en masse?

(lots of people input numbers into this file so how can i make sure it imports properly in the future so i don't have to go and manually change their inputs)

Super Contributor
Posts: 297

Re: importing data from excel - some data points not importing

If this is a file you only import from and not export to then I would use data validation in excel to limit the values to numeric only.

If you both import and export to this file then you are going to override the data validation as part of the export.  I would import the data as text.  I would then clean the text using the SAS COMPRESS function and convert it to numeric.

You could even do this if turn excel data validation on just to be safe...I would.

Super User
Posts: 7,111

Re: importing data from excel - some data points not importing

Save the file as .csv and import it with a data step. Either SAS will tolerate the blanks, or you can do the initial input as character and then convert in the data step.

Saving to a text file also gives you the luxury of visually inspecting what data you try to read.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,565

Re: importing data from excel - some data points not importing

First, I would agree with KurtBremser, CSV + datastep is the way with most control over what you are doing.

I you are fortunate enough to be able to change the Excel file, then adding Validation into the spreadsheet upfront so they can only enter specific data will help a fair.  Excel however isn't a database/data entry/transport system and so you will still get problems, for instance you have no versioning on the data, no access control, no fixed structure etc.

One other option is to have a VBA macro which will process the sheet and save a CSV for you.  Obviously you need to know a bit of VBA, but its relatively straight forward.  In the macro you could run every cell through a validation script and then export to CSV.  Here is a starter page: http://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx

Super Contributor
Posts: 297

Re: importing data from excel - some data points not importing

Something like this:

DATA HAVE;
HAVE1 = "123A";
HAVE2 = "123 ";
RUN;

DATA WANT;
SET HAVE;
WANT1 = INPUT(COMPRESS(HAVE1,"1234567890","K"),8.);
WANT2 = INPUT(COMPRESS(HAVE2,"1234567890","K"),8.);
RUN;

Super User
Posts: 9,779

Re: importing data from excel - some data points not importing

Add  an option  mixed=yes  into your code .

It seems 124 125 has some characters, but SAS force to take it as a numeric type variable.

Xia Keshan

Ask a Question
Discussion stats
  • 9 replies
  • 424 views
  • 1 like
  • 6 in conversation