Numbering rows according to certain rule

[lioradam]

Hello,

I have the attached data set, in which the first 5 variables are part of the data set and the last variable, "year_of_tenure", is the variable I  want to calculate through SAS code.

The variable "year_start01" is the year the tenure starts

The variable "year_end01" is the year the tenure ends

The variable "year" include several years within this tenure range. This variable doesn't include all the years of the tenure. I would like to calculate the variable "year_of_tenure", so if for example "year_start01" is 2000, then near the year "2001" the "year_of_tenure" will be 2.

Thanks in advance,

Lior

/* --------------------------------------------------------------------
   Code generated by a SAS task
   
   Generated on Sunday, November 6, 2022 at 12:47:56 PM
   By task:     Import Data Wizard
   
   Source file: C:\Users\Lior\Downloads\year_of_tenure.xlsx
   Server:      Local File System
   
   Output data: WORK.year_of_tenure
   Server:      Local
   -------------------------------------------------------------------- */

/* --------------------------------------------------------------------
   This DATA step reads the data values from DATALINES within the SAS
   code. The values within the DATALINES were extracted from the Excel
   source file by the Import Data wizard.
   -------------------------------------------------------------------- */

DATA WORK.year_of_tenure;
    LENGTH
        GVKEY01          $ 6
        Full_Name        $ 22
        year_start01       8
        year_end01         8
        year               8
        year_of_tenure     8 ;
    FORMAT
        GVKEY01          $CHAR6.
        Full_Name        $CHAR22.
        year_start01     BEST12.
        year_end01       BEST12.
        year             BEST12.
        year_of_tenure   BEST12. ;
    INFORMAT
        GVKEY01          $CHAR6.
        Full_Name        $CHAR22.
        year_start01     BEST12.
        year_end01       BEST12.
        year             BEST12.
        year_of_tenure   BEST12. ;
    INFILE DATALINES4
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        GVKEY01          : $CHAR6.
        Full_Name        : $CHAR22.
        year_start01     : BEST32.
        year_end01       : BEST32.
        year             : BEST32.
        year_of_tenure   : BEST32. ;
DATALINES4;
001034Ingrid Wiik2000200520012
001034Ingrid Wiik2000200520023
001034Ingrid Wiik2000200520034
001034Ingrid Wiik2000200520045
001078Miles D. White, M.B.A.1999201920024
001078Miles D. White, M.B.A.1999201920035
001078Miles D. White, M.B.A.1999201920046
001078Miles D. White, M.B.A.1999201920057
001078Miles D. White, M.B.A.1999201920068
001078Miles D. White, M.B.A.1999201920079
001078Miles D. White, M.B.A.19992019200810
001078Miles D. White, M.B.A.19992019200911
001078Miles D. White, M.B.A.19992019201012
001078Miles D. White, M.B.A.19992019201113
001078Miles D. White, M.B.A.19992019201214
001078Miles D. White, M.B.A.19992019201315
001078Miles D. White, M.B.A.19992019201416
001078Miles D. White, M.B.A.19992019201517
001078Miles D. White, M.B.A.19992019201618
001078Miles D. White, M.B.A.19992019201719
001078Miles D. White, M.B.A.19992019201820
001078Miles D. White, M.B.A.19992019201921
001177John W. Rowe2001200520011
001177John W. Rowe2001200520032
001177John W. Rowe2001200520043
001177John W. Rowe2001200520054
;;;;

[Kurt_Bremser · Accepted solution]

data want;
set year_of_tenure;
by gvkey01;
retain start;
if first.gvkey01 then start = year_start01;
year_of_tenure = year - start + 1;
drop start;
run;

[lioradam]

Great, thanks.
Lior