SQL with missing values

finsangel — Fri, 05 Jul 2013 08:36:26 GMT

Hi everyone,

I am quite new on SAS and now I'd like to do something like sql. The case is as below. I have a main data set(called data1) that contains information of variables var_a,var_b,var_c like

var_a var_b var_c

1 Eur 1550

2 Asi 1750

5 3000

6 Afr 1500

... ... ...

Some information are missing in the table. In the meanwhile, I have a small table(data2) that I want to merge to data1. var_aa and var_a are common variables. The same for var_b and var_c.

Here is the data2.

var_aa var_bb var_cc coeff

1 Eur <1600 1.2

2 Asi 1750 1.6

4 US 2000 1.9

other Eur 3.4

5 Afr 1.0

You see that, first row of data1 correponds to first row of data2, second of data1 correponds to second of data2. third row of data three has no value for var_b, so it correponds to row 6 of data2. But fourth row doesn't have match in data2. In the sql, I want SAS to add coeff "2.5" to all rows in data1 that don't have a match in data2 and add the matched coeffs to the rows that have exact matchs in data2.

My data1 has around 40000 rows and my data2 has around 200 rows. So I think that will be difficult to arrage excel sheets first and then import into SAS. Would you have any suggestion to manipulate this strange sql cas???

Thanks a lot everyone,

Re: SQL with missing values

shivakrishna — Fri, 05 Jul 2013 11:45:10 GMT

can you please give a sample output you need.

shiva

Re: SQL with missing values

esjackso — Fri, 05 Jul 2013 12:11:50 GMT

Try something like this:

Proc sql;

create table new as

select a.*, b.coeff

from data1 as a left join data2 as b

on a.var_a = b.var_aa

;

quit;

data new2;

set new;

if coeff = . then coeff = 2.5;

run;

Re: SQL with missing values

Tom — Fri, 05 Jul 2013 15:16:03 GMT

You can eliminate the second step by using the COALESCE() function in the original select.

coalesce(b.coeff,2.5) as coeff

topic Re: SQL with missing values in SAS Programming

SQL with missing values

Re: SQL with missing values

Re: SQL with missing values

Re: SQL with missing values