DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 162
Accepted Solution

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

[ Edited ]

 

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


Accepted Solutions
Solution
‎05-19-2017 08:24 PM
Respected Advisor
Posts: 3,887

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

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 17,784

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

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

 

 

Regular Contributor
Posts: 162

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

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

Solution
‎05-19-2017 08:24 PM
Respected Advisor
Posts: 3,887

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

[ Edited ]

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

Regular Contributor
Posts: 162

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

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 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 198 views
  • 2 likes
  • 3 in conversation