Defining conditions when two datasets are merged

joon1 — Sat, 24 Jun 2023 00:49:29 GMT

Dear Madam/Sir,

I would like to add conditions when two datasets are merged.

That is, how can we add conditions 'when cartel_byear=<cyear<=cartel_eyear then cartel_year=1; else cartel_year=0' when c2 (firm-year population) and ca3 are merged.

proc sql undo_policy=none;
create table c3 as select
c2.*,ca3.cartel_byear, ca3.cartel_eyear
from c2 left join ca3 on
when cartel_byear=<cyear<=cartel_eyear then cartel_year=1; else cartel_year=0;
(c2.gvkey=ca3.gvkey) ;
quit;

dataset ca3

Obs gvkey cartel_byear cartel_eyear12345

178698	2000	2006
21542	2004	2013
21542	2003	2014
21542	2004	2014
1004	1987	1995

dataset c2 (firm-year population)

Obs GVKEY FYEAR12345

001000	1970
001000	1971
001000	1972
001000	1973
001000	1974

Any help will be highly appreciated.

Sincerely,

Joon

Re: Defining conditions when two datasets are merged

Patrick — Sat, 24 Jun 2023 02:52:47 GMT

If you want us to provide tested code then please provide your sample data in the form of a working SAS data step. Take the discussion here as an example how to provide such information.

If I understand right then you want to create a new variable with a value populated on some condition. That would be done in the SELECT statement via a CASE expression.
Docu for CASE is here.

Below code not tested.

proc sql undo_policy=none;
  create table c3 as 
    select
      c2.*
      ,ca3.cartel_byear 
      ,ca3.cartel_eyear
      ,case
        when ca3.cartel_byear=<cyear<=ca3.cartel_eyear then 1 
        else 0
        end as cartel_year
    from c2 left join ca3 
      on c2.gvkey=ca3.gvkey
  ;
quit;

From the data provided it was unclear to me from which source table cyear is coming. You will eventually have to add the table alias to it for things to work. If the variable only exists in one of the tables then things will also work without that alias - but it's cleaner to always add it.
ca3.cartel_byear=<cyear<=ca3.cartel_eyear

topic Defining conditions when two datasets are merged in SAS Programming

Defining conditions when two datasets are merged

Re: Defining conditions when two datasets are merged