Creating a Variable That Shows the First Time an Observation Occurs

[larusso522]

I have a dataset similar to this. I would like to add a variable to this dataset called EN which tells me the first period(NumPRD) DF (in the COMPANY column) occurs for each market.

Data have;
input Year    Quarter    Market   $ NUMPRD COMPANY;
cards;

2015   1              ASL         1                  AD              
2015   2              ASL         2                  DD    

2015   3              ASL         3                  DF              
2015   4              ASL         4                  DF              
2015   1              NWR       1                  DD              
2015   2              NWR       2                  AD              
2015   3              NWR       3                  DD              
2015   4              NWR       4                  DD              

2016   1              NWR       5                  DF              
2016   2              NWR       6                  AD              
2016   3              NWR       7                  DD              
2016   4              NWR       8                  DF 
;

In the dataset above, DF occurs for the first time in period 3 for market ASL and in period 5 for market NWR.  I would like to have this table:

Year    Quarter    Market   $ NUMPRD COMPANY   EN

2015   1              ASL         1                  AD               3
2015   2              ASL         2                  DD               3
2015   3              ASL         3                  DF               3
2015   4              ASL         4                  DF               3
2015   1              NWR       1                  DD               5
2015   2              NWR       2                  AD               5
2015   3              NWR       3                  DD               5
2015   4              NWR       4                  DD               5

2016   1              NWR       5                  DF               5
2016   2              NWR       6                  AD               5
2016   3              NWR       7                  DD               5
2016   4              NWR       8                  DF               5

[MarkWik]

where is FO in the wanted table. Can you please explain your logic with your wanted table. 

Or perhaps, i'm not comprehending your English well

[larusso522]

I meant EN.

[PGStats]

data want;
do until(last.market);
    set have; by market notsorted;
    if missing(EN) then if company = "DF" then EN = numprd;
    end;
do until(last.market);
    set have; by market notsorted;
    output;
    end;
run;

This should do it efficiently:

[Patrick]

@larusso522

Below using a hash table.

Data have;
  input Year Quarter Market $ NUMPRD COMPANY $;
  cards;
2015 1 ASL 1 AD 
2015 2 ASL 2 DD 
2015 3 ASL 3 DF 
2015 4 ASL 4 DF 
2015 1 NWR 1 DD 
2015 2 NWR 2 AD 
2015 3 NWR 3 DD 
2015 4 NWR 4 DD 
2016 1 NWR 5 DF 
2016 2 NWR 6 AD 
2016 3 NWR 7 DD 
2016 4 NWR 8 DF 
;
run;

data want(drop=_:);

  set have;
  by market year quarter;

  if _n_=1 then
    do;
      if 0 then set have(keep=numprd rename=(numprd=en));
      dcl hash h1(dataset:'have(where=(company="DF") keep=market company numprd rename=(numprd=en))', multidata:'n');
      h1.defineKey('market');
      h1.defineData('en');
      h1.defineDone();
    end;
  _rc=h1.find();

run;

[Ksharp · Accepted solution]

Data have;
  input Year Quarter Market $ NUMPRD COMPANY $;
  cards;
2015 1 ASL 1 AD 
2015 2 ASL 2 DD 
2015 3 ASL 3 DF 
2015 4 ASL 4 DF 
2015 1 NWR 1 DD 
2015 2 NWR 2 AD 
2015 3 NWR 3 DD 
2015 4 NWR 4 DD 
2016 1 NWR 5 DF 
2016 2 NWR 6 AD 
2016 3 NWR 7 DD 
2016 4 NWR 8 DF 
;
run;
data temp;
 set have(where=(COMPANY='DF'));
 by  Market;
 if first.Market;
 keep Market NUMPRD ;
run;
data want;
 merge have temp(rename=(NUMPRD=en));
 by Market;
run;

[novinosrin]

Data have;
  input Year Quarter Market $ NUMPRD COMPANY $;
  cards;
2015 1 ASL 1 AD 
2015 2 ASL 2 DD 
2015 3 ASL 3 DF 
2015 4 ASL 4 DF 
2015 1 NWR 1 DD 
2015 2 NWR 2 AD 
2015 3 NWR 3 DD 
2015 4 NWR 4 DD 
2016 1 NWR 5 DF 
2016 2 NWR 6 AD 
2016 3 NWR 7 DD 
2016 4 NWR 8 DF 
;
run;

proc sql;
create table want as
select a.*,b.en
from 
have a left join (select market, min(numprd) as en from have where company='DF' group by market) b
on a.market=b.market
order by year,market,quarter;
quit;