## Creating a Variable That Shows the First Time an Observation Occurs

Solved
Occasional Contributor
Posts: 10

# 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   2              ASL         2                  DD

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

2016   1              NWR       5                  DF
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

``````Data have;
input Year Quarter Market \$ NUMPRD COMPANY \$;
cards;
2015 2 ASL 2 DD
2015 3 ASL 3 DF
2015 4 ASL 4 DF
2015 1 NWR 1 DD
2015 3 NWR 3 DD
2015 4 NWR 4 DD
2016 1 NWR 5 DF
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;
``````

All Replies
Frequent Contributor
Posts: 102

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

[ Edited ]

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

I meant EN.

Posts: 5,477

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

``````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
Posts: 4,668

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

@larusso522

Below using a hash table.

``````Data have;
input Year Quarter Market \$ NUMPRD COMPANY \$;
cards;
2015 2 ASL 2 DD
2015 3 ASL 3 DF
2015 4 ASL 4 DF
2015 1 NWR 1 DD
2015 3 NWR 3 DD
2015 4 NWR 4 DD
2016 1 NWR 5 DF
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

``````Data have;
input Year Quarter Market \$ NUMPRD COMPANY \$;
cards;
2015 2 ASL 2 DD
2015 3 ASL 3 DF
2015 4 ASL 4 DF
2015 1 NWR 1 DD
2015 3 NWR 3 DD
2015 4 NWR 4 DD
2016 1 NWR 5 DF
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

``````Data have;
input Year Quarter Market \$ NUMPRD COMPANY \$;
cards;
2015 2 ASL 2 DD
2015 3 ASL 3 DF
2015 4 ASL 4 DF
2015 1 NWR 1 DD
2015 3 NWR 3 DD
2015 4 NWR 4 DD
2016 1 NWR 5 DF
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.