topic Re: PROC SQL: can you replace missing value(s)?? in SAS Programming

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

G_I_Jeff — Thu, 15 Oct 2015 15:09:32 GMT

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.

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

dsbihill — Thu, 15 Oct 2015 15:15:55 GMT

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

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

LinusH — Thu, 15 Oct 2015 15:24:55 GMT

Why SQL and not a data step?

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

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

G_I_Jeff — Thu, 15 Oct 2015 15:24:57 GMT

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.

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

G_I_Jeff — Thu, 15 Oct 2015 15:26:24 GMT

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.

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

Haikuo — Thu, 15 Oct 2015 17:12:17 GMT

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;