BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
larusso522
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

6 REPLIES 6
MarkWik
Quartz | Level 8

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

PGStats
Opal | Level 21
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
Patrick
Opal | Level 21

@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
Super User
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
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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