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,
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;
can you please give a sample output you need.
shiva
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;
You can eliminate the second step by using the COALESCE() function in the original select.
coalesce(b.coeff,2.5) as coeff
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.