create dummy by ticker and year

[sasphd]

Hello, 

I want de create a variable tag1 which takes a value of 1 if by year et ticker

TABLE HAVE

ticker  FYEAR

A           2015

A           2016

A           2016

B          2015

B          2016

B           2017

B            2017

TABLE WANT

ticker  FYEAR     TAG1

A           2015        1

A           2016        1

A           2016        0

B          2015        1

B          2016        1

B           2017       1

B            2017      0

thanks!

[PaigeMiller]

So any record is a duplicate year for a given ticker gets a TAG1 value of 0? Or are the rules more complicated than that? From such a small example, it's hard to deduce rules. Please tell us.

[ballardw · Accepted solution]

Perhaps:

data HAVE;
input ticker $  FYEAR ;
datalines;
A           2015
A           2016
A           2016
B          2015
B          2016
B           2017
B            2017
;

data want;
   set have;
   by ticker fyear;
   tag1= first.fyear;
run;

The above assumes the data is sorted by ticker and fyear. If the actual is not sorted but is grouped then adding the NOTSORTED option to the BY statement in the Data Want step may work. If not, then sort the data first is likely the approach.

When you use a BY statement SAS creates automatic variables First.Variablename and Last.Variable name that indicate whether an observation is the first or last of the by group for each variable. These values are 1, for true, and 0, for false.