DATA Step, Macro, Functions and more

SQL with missing values

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SQL with missing values

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,


Accepted Solutions
Solution
‎07-05-2013 08:11 AM
Super Contributor
Posts: 334

Re: SQL with missing values

Posted in reply to finsangel

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;

View solution in original post


All Replies
Contributor
Posts: 56

Re: SQL with missing values

Posted in reply to finsangel

can you please give a sample output you need.

shiva

Solution
‎07-05-2013 08:11 AM
Super Contributor
Posts: 334

Re: SQL with missing values

Posted in reply to finsangel

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;

Super User
Super User
Posts: 7,039

Re: SQL with missing values

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

coalesce(b.coeff,2.5) as coeff

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 193 views
  • 0 likes
  • 4 in conversation