DATA Step, Macro, Functions and more

Creating a Variable That Shows the First Time an Observation Occurs

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Creating a Variable That Shows the First Time an Observation Occurs

[ Edited ]

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

 

 


Accepted Solutions
Solution
a week ago
Super User
Posts: 10,686

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

Posted in reply to larusso522
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;

View solution in original post


All Replies
Frequent Contributor
Posts: 102

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

[ Edited ]
Posted in reply to larusso522

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

Occasional Contributor
Posts: 10

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

I meant EN.

Esteemed Advisor
Posts: 5,477

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

Posted in reply to larusso522
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:

 

PG
Respected Advisor
Posts: 4,668

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

Posted in reply to larusso522

@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;
Solution
a week ago
Super User
Posts: 10,686

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

Posted in reply to larusso522
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;
PROC Star
Posts: 1,564

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

Posted in reply to larusso522
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;
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 146 views
  • 0 likes
  • 6 in conversation