BookmarkSubscribeRSS Feed
G_I_Jeff
Obsidian | Level 7

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.

5 REPLIES 5
dsbihill
Obsidian | Level 7

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
LinusH
Tourmaline | Level 20

Why SQL and not a data step?

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

Data never sleeps
G_I_Jeff
Obsidian | Level 7
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.
G_I_Jeff
Obsidian | Level 7

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.

Haikuo
Onyx | Level 15

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;

 

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