BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
finsangel
Calcite | Level 5

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,

1 ACCEPTED SOLUTION

Accepted Solutions
esjackso
Quartz | Level 8

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

3 REPLIES 3
shivakrishna
Fluorite | Level 6

can you please give a sample output you need.

shiva

esjackso
Quartz | Level 8

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;

Tom
Super User Tom
Super User

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

coalesce(b.coeff,2.5) as coeff

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