PROC SQL: can you replace missing value(s)??

Reply
Frequent Contributor
Posts: 75

PROC SQL: can you replace missing value(s)??

Simple data pull, not merging tables or anything. Can I replace null values in PROC SQL statement like I can in the following DATA satement:

 

DATA TABLE1;

  SET TABLE2;

    IF OBS1 = . THEN OBS1 = 0;

RUN;

 

I've looked at the IFN function but cannot get it to work or find any example to reference.

Contributor
Posts: 53

Re: PROC SQL: can you replace missing value(s)??

If statements dont function in proc sql but you can do either of the following.

 

Case
      when obs1 = . then 0
      else obs1
end as obs1

or you can simply do this

 

sum(obs1, 0) as obs1
Super User
Posts: 5,433

Re: PROC SQL: can you replace missing value(s)??

Why SQL and not a data step?

IMHO the most elegant solution is the coalesce() coalescec() functions.

Data never sleeps
Frequent Contributor
Posts: 75

Re: PROC SQL: can you replace missing value(s)??

Just trying to clean up code and learn different ways honestly. If I can't get my SQL code to work the way I want I always revert back to the sturdy DATA statement.
Frequent Contributor
Posts: 75

Re: PROC SQL: can you replace missing value(s)??

I am actually trying to perform an AVG function in a simple SQL statement:

PROC SQL;
SELECT OBS1,
OBS2,
AVG(OBS3) AS AVGOBS
FROM TABLE1;
QUIT;

I'm trying to graph results but null values throws the results off.

Respected Advisor
Posts: 3,156

Re: PROC SQL: can you replace missing value(s)??

If just to replace the missing value, A simple SUM should do:

PROC SQL;
SELECT OBS1,
OBS2,
SUM(OBS3,0) AS OBS3
FROM TABLE1;
QUIT;

 

Ask a Question
Discussion stats
  • 5 replies
  • 317 views
  • 0 likes
  • 4 in conversation