DATA Step, Macro, Functions and more

missing values

Reply
Contributor
Posts: 61

missing values

Hello, I would like to ask you a question about missing values. Before i ask a question i want to tell a story.

Assume, i have two variables in sas, one numeric and character and in every one i have missing values, i.e in numeric variable the missing value will mark as .  ,and in character masr as " ". Now export my data set to sql database. When i will open my table in sql, i will see my missing values, as NULL, and it  is no difference between numeric or character. In the both of them i will see NULL instead of missing values.

1. First Question : i am right about the symbol of missing value in sql i.e i will see NULL in both of them?

Now i want  to export table from sql to two sources : one is sas and the second is csv file. When i import to sas with proc import from sql, sas know recognize missing value in numeric(.) and missing value in character(" "). 

2. Second Question : Sas know to read metadata of table in sql, he knows to read types of variables from specific table in sql?

When i import to csv file or other formats, i see word NULL in every missing values, and when i read this csv file to sas with proc import, sas cannot recognize numeric variable vs chatacter variables. For example, if in the first 10 rows, there are numbers, then Sas state that it is numeric variables, the problem that in row 150 you have value "NULL" and then you got an error.

3.Third Question : How can i read csv(or other format) file  in sas with proc import? how can i decide the problem of missing values?

Maybe there are a smart decision?

Thank you a lot

 

 

 

 

Super User
Super User
Posts: 7,988

Re: missing values

Sorry, bit hard to decphier, let me see if I understand right:

1)  Yes, NULL is for all datatypes in SQL.

2)  Not really no.

3)  I would advise not to use proc import - it is a guessing procedure so you may not get what you want.

 

Now, lets step back from there.  SAS is able to interact directly with most databases.  You can use various methods to do this, pass-through for instance takes the statements from the proc sql and sends them to the database.  There are other methods for processing in SAS.  There are many helpfiles out there on how to extract data from a database:

http://www.lexjansen.com/nesug/nesug04/io/io04.pdf

For example.

Note that for any data transfer I would expect you as the data owner to know the structure and format of the data, and this is the same for CSV import - you specify the datastep import, what formats to read the data in.

Super User
Posts: 19,862

Re: missing values

2. SAS will can handle most DB datatypes reasonably. Usually text, numbers, and datetimes are handled easily.  When you get into blobs and clobs it can vary b

 

3 Know thy data. When exporting from SQL perhaps export a table structure doc (data dictionary) as well and then develop your SAS programs to work off that file for importing data. This is under the assumption this is something you do on a regular database. 

Super User
Posts: 11,343

Re: missing values

Generally when I run into data that have to read into SAS that contains NULL I use  custom informats to assign NULL to missing.

Which is not a big deal as I often end up using the custom informat to do other things with categorical type data.

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 826 views
  • 0 likes
  • 4 in conversation