topic Re: create dummy by ticker and year in SAS Procedures

create dummy by ticker and year

sasphd — Wed, 05 Jun 2024 17:50:56 GMT

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

B 2015

B 2016

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!

Re: create dummy by ticker and year

PaigeMiller — Wed, 05 Jun 2024 17:58:14 GMT

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.

Re: create dummy by ticker and year

ballardw — Wed, 05 Jun 2024 17:58:49 GMT

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.