BookmarkSubscribeRSS Feed
AlexeyS
Pyrite | Level 9

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

 

 

 

 

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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. 

ballardw
Super User

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.

 

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 1986 views
  • 0 likes
  • 4 in conversation