topic Re: How to assign a default value to a variable during the join in SAS Programming

How to assign a default value to a variable during the join

Sheeba — Fri, 19 May 2017 23:09:00 GMT

Hi,

I have a question related to SAS join.

I have a dataset one which has the variable country, year,state

Country year state match

USA 2017` CA Y
USA 2017 PA y

USA 2016 TX y

I have dataset two which is a lookup table with few rows and has the columns country,year state , region.

Country year state region

USA           2017 CA   R1
USA          2017            R2
USA           2017 PA    R3

I am looking to attach region to the dataset one

Proc sql;

Select a.* ,b.region from one a

Left join

Two b

On a. year=b.year and

a.country =b. country

and a.state=b.state;

quit;

is there any way I can modify the above code so that for all the states except , CA and PA(states which are existing in the second table) the value of R2 gets assigned during the join..

Editing to add more details.

for a record in dataset one if the country is USA and year is 2017 and state is CA the region needs to be R1,if the country is USA and year is 2017 and state is PA the region needs to be R3 and for all the other states whose year is 2017 and country is USA the region needs to be r2

Thanks in advance,

regards,

Sheeba

Re: How to assign a default value to a variable during the join

Reeza — Fri, 19 May 2017 23:23:01 GMT

That sounds like a CASE statement. The only issue I can see is using the wildcard (*) if the names overlap, if this is a new variable it will be fine.

Sample - untested.

Case when state='CA' then R2

when state='PA' then r3

end as new_variable

Re: How to assign a default value to a variable during the join

Patrick — Sat, 20 May 2017 00:56:42 GMT

Assuming that there might also be other countries in your data and R2 only applies to USA the following should work:

data one;
  input (Country year state match) (:$);
  datalines;
USA 2017 CA Y
USA 2017 PA y
USA 2016 TX y
run;

data two;
  infile datalines truncover dsd dlm=' ';
  input (Country year state region) (:$);
  datalines;
USA 2017 CA R1
USA 2017  R2
USA 2017 PA R3
;
run;

Proc sql;
  Select 
      a.* ,
      case
        when(a.country='USA') then coalesce(b.region,'R2') 
        else b.region
        end as region 
    from one a
    Left join
        Two b
        on a.country=b.country and
        a.year=b.year and
        and a.state=b.state;
quit;

If you've got only USA then remove the CASE as the COALESCE() bit will do.

coalesce(b.region,'R2') as region

Re: How to assign a default value to a variable during the join

Sheeba — Sat, 20 May 2017 00:19:08 GMT

Hi Reeza,

Thanks a lot for the reply.

I will try out the case statements. I need to have it as a same variable as i am using it in the same program. I will try to modify this.

regards,

Sheeba

Re: How to assign a default value to a variable during the join

Sheeba — Sat, 20 May 2017 00:24:09 GMT

Hi Patrick,

Thanks a lot for the detailed reply.

This is really helpful. Country is likely to remain the same , the year might change. I will modify the case statement accordingly.

Regards,

Sheeba