Lag function within IDs with BY statement - help needed

Reply
New Contributor
Posts: 2

Lag function within IDs with BY statement - help needed

I am trying to create a single row of data for longitudinal files that uses the LAG function to create a single record. I’ve gotten close, but can’t get the selection correct to only report the data in the first row.

Sample data code below, as well as my intended output.

Thanks a million to whomever can assist.

Jon

This is what I want:

PersonID_n

LastName

JobChangeCode

ID_obs

seqno

lagstart

Move1_ID

Move1_name

Move1_JobCode

Move2_ID

Move2_name

Move2_JobCode

Move3_ID

Move3_name

Move3_JobCode

565

Fialdini

10

1

1

1

565

Fialdini

10

565

Fialdini

9

565

Fialdini

8

565

Fialdini

9

2

2

.

.

.

.

.

.

.

565

Fialdini

8

3

3

.

.

.

.

.

.

.

565

Fialdini

8

4

4

.

.

.

.

.

.

.

565

Fialdini

7

5

5

.

.

.

.

.

.

.

565

Fialdini

6

6

6

.

.

.

.

.

.

.

565

Fialdini

6

7

7

.

.

.

.

.

.

.

574

Banua

20

1

8

1

574

Banua

20

574

Banua

18

574

Banua

18

574

Banua

18

2

9

.

.

.

.

.

.

.

574

Banua

18

3

10

.

.

.

.

.

.

.

574

Banua

17

4

11

.

.

.

.

.

.

.

574

Banua

15

5

12

.

.

.

.

.

.

.

576

Young

15

1

13

1

576

Young

15

576

Young

12

576

Young

11

576

Young

12

2

14

.

.

.

.

.

.

.

576

Young

11

3

15

.

.

.

.

.

.

.

576

Young

10

4

16

.

.

.

.

.

.

.

576

Young

10

5

17

.

.

.

.

.

.

.

CODE:

data old;

input PersonID_n 1-3 LastName $4-12 JobChangeCode 13-20;

datalines;

565 Fialdini    10

565 Fialdini    9

565 Fialdini    8

565 Fialdini    8

565 Fialdini    7

565 Fialdini    6

565 Fialdini    6

574   Banua 20

574   Banua 18

574   Banua 18

574   Banua 17

574   Banua 15

576   Young 15

576   Young 12

576   Young 11

576   Young 10

576   Young 10

;

DATA old2; SET old;

BY PersonID_n;

IF FIRST.PersonID_n THEN ID_obs = 1;

ELSE ID_obs + 1;

seqno = _n_ ;

RUN;

*Create do loop with LAG for max 15 transactions to flatten file of career movement - only on 3 people to test;

data new; set old2 (obs = 17);

BY PersonID_n;

/*Create lagstart dummy*/

if first.PersonID_n then lagstart=1;

if first.PersonID_n THEN DO;

Move1_ID = PersonID_n;

Move1_name = (lastname);

Move1_JobCode = (JobChangeCode);

Move2_ID = lag(PersonID_n);

Move2_name = lag(lastname);

Move2_JobCode = lag(JobChangeCode);

Move3_ID = lag2(PersonID_n);

Move3_name = lag2(lastname);

Move3_JobCode = lag(JobChangeCode);

END;

run;

proc print;

run;

Super User
Posts: 17,828

Re: Lag function within IDs with BY statement - help needed

New Contributor
Posts: 2

Re: Lag function within IDs with BY statement - help needed

I am only using this as an example. The focus is really on the changes in JobChangeCode and other sensitive personnel data.

Transpose was not working for me either earlier, but I may poke around some more with it.

Ask a Question
Discussion stats
  • 2 replies
  • 158 views
  • 3 likes
  • 2 in conversation