BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sheeba
Lapis Lazuli | Level 10

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

4 REPLIES 4
Reeza
Super User

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

 

 

Sheeba
Lapis Lazuli | Level 10

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

Patrick
Opal | Level 21

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

Sheeba
Lapis Lazuli | Level 10

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 

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