SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to import excel file without missing values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

How to import excel file without missing values

DATA Description

We have an Excel Sheet in that we have 1,000,000 Observations and 150 Variables.

Some of the Records(Entire Row) have Missing values.

TASK

Import the Excel Sheet into SAS Data Set with the best available technique . Remove all Missing Records in the SAS Data Set(If possible import only Non Missing Records).
Now we need to create Two SAS Date Sets "ONE" and "TWO".
Data Set "ONE" should have all the Character Variables with 25,000 Random Records.


Data Set "TWO" should have all the Numeric Variables with 50,000 Random Records.


Accepted Solutions
Solution
‎09-16-2014 03:26 AM
Valued Guide
Posts: 3,208

Re: How to import excel file without missing values

I this a real-life question? 

Excel I not a professional tool for date-entry. Your first question is about data-cleansing.

The best way is converting it from excel to a text/csv file. Reading this file with input statements eliminates all uncertainty about "guessing rows" used in those entry level import approaches. An all missing fields can be dealt with. Numerics/chars are well defined as you are coding those. Your brain has to decide.

A split with one/two dataset looks to be the validate/training approach of data mining.  But that split in numeric/char is a weird one. In the digitial age this is one to think first of what is the meaning. You should think in category/measure and by that some specials of that with date/time and geography.    

For the technical part in SAS there are variable lists. SAS(R) 9.4 Language Reference: Concepts, Third Edition there are special ones as _numeric_ and _char_.

When you want to be technical SAS stores those physical grouped like that ann not in the logical order of creation.

---->-- ja karman --<-----

View solution in original post


All Replies
Solution
‎09-16-2014 03:26 AM
Valued Guide
Posts: 3,208

Re: How to import excel file without missing values

I this a real-life question? 

Excel I not a professional tool for date-entry. Your first question is about data-cleansing.

The best way is converting it from excel to a text/csv file. Reading this file with input statements eliminates all uncertainty about "guessing rows" used in those entry level import approaches. An all missing fields can be dealt with. Numerics/chars are well defined as you are coding those. Your brain has to decide.

A split with one/two dataset looks to be the validate/training approach of data mining.  But that split in numeric/char is a weird one. In the digitial age this is one to think first of what is the meaning. You should think in category/measure and by that some specials of that with date/time and geography.    

For the technical part in SAS there are variable lists. SAS(R) 9.4 Language Reference: Concepts, Third Edition there are special ones as _numeric_ and _char_.

When you want to be technical SAS stores those physical grouped like that ann not in the logical order of creation.

---->-- ja karman --<-----
Super User
Posts: 10,490

Re: How to import excel file without missing values

Since this looks sort of like homework instead of a complete solution for the second part of the question:

Look at proc surveyselect, using a KEEP dataset option of _numeric_ or _character_ on the input data set.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 865 views
  • 2 likes
  • 3 in conversation