topic Re: Creating a Variable That Shows the First Time an Observation Occurs in SAS Programming

Creating a Variable That Shows the First Time an Observation Occurs

larusso522 — Mon, 11 Jun 2018 02:29:59 GMT

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

Re: Creating a Variable That Shows the First Time an Observation Occurs

MarkWik — Mon, 11 Jun 2018 02:25:45 GMT

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

Re: Creating a Variable That Shows the First Time an Observation Occurs

larusso522 — Mon, 11 Jun 2018 02:35:25 GMT

I meant EN.

Re: Creating a Variable That Shows the First Time an Observation Occurs

PGStats — Mon, 11 Jun 2018 03:40:53 GMT

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:

Re: Creating a Variable That Shows the First Time an Observation Occurs

Patrick — Mon, 11 Jun 2018 04:31:55 GMT

@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;

Re: Creating a Variable That Shows the First Time an Observation Occurs

Ksharp — Mon, 11 Jun 2018 12:48:15 GMT

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;

Re: Creating a Variable That Shows the First Time an Observation Occurs

novinosrin — Mon, 11 Jun 2018 18:00:18 GMT

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;