LAG function specific to each person, not just previous row

[1SasUser1]

Hi,

I am using SAS Enterprise Guide 5.1 with the following code:

data work.test;
set WORK.QUERY;

by Number;

if first.Number then Recovered = .;
else if lag(Level) > Level then Recovered = 1; else Recovered = 0;
if first.Number then Rolled = .;
else if lag(Level) < Level then Rolled = 1; else Rolled = 0;
if first.Number then Stable = .;
else if lag(Level) = Level then Stable = 1; else Stable = 0;
run;

I am trying to count the number of occasions a person rolls through a level and how many times they recover. I have been using the LAG function but it seems to check the row before which could be a different person. I tried setting the first.person = . and then starting my IF function, but it shifted everything down.

I tried it for one person before the full data set, but it didn't work. Hopefully below shows it clearer with what I want:

Columns to use	Columns to use	What I want it to show	What I want it to show	What I want it to show	What it shows	What it shows	What it shows
Number	Level	Rolled	Recovered	Stable	Rolled	Recovered	Stable
12300000	0	.	.	.
12300000	0	0	0	1
12300000	1	1	0	0
12300000	0	0	1	0
12300000	1	1	0	0
12300000	2	0	0	0
12300000	3	0	0	0
12300000	4	0	0	0
12300000	5	0	0	0
45600000	0	.	.	.	.	.	.
45600000	0	0	0	1	1	0	0
45600000	0	0	0	1	0	0	1
45600000	1	1	0	0	1	0	0
45600000	0	0	1	0	0	1	0
45600000	0	0	0	1	0	0	1
45600000	0	0	0	1	0	0	1
45600000	0	0	0	1	0	0	1
45600000	0	0	0	1	0	0	1
45600000	0	0	0	1	0	0	1
45600000	0	0	0	1	0	0	1
45600000	0	0	0	1	0	0	1
78900000	0	.	.	.
78900000	0	0	0	1
78900000	1	1	0	0
78900000	0	0	1	0
78900000	1	1	0	0
78900000	0	0	1	0
78900000	1	1	0	0
78900000	1	0	0	1
78900000	2	1	0	0
78900000	3	0	0	0
78900000	3	0	0	1
78900000	4	1	0	0

As you can see when the level goes from 0-5 consecutively then it should only be counted as 1 roll. If the previous row has the same value e.g. 0 then 0, then it is stable. If the previous row has a higher level then it is counted as a recover.

Any help would be greatly appreciated!

[andreas_lds]

Afaik calling lag three times generates three value lists and you need to call lag when first.Number so that the first value is stored in the list. Try:

data work.test;
   set WORK.QUERY;
   by Number;

   laggedLevel = lag(Level);

   if first.Number then do;
      call missing(Recovered, Rolled, Stable);
   end;
   else do;      
      Recovered = (laggedLevel > Level);
      Rolled =  (laggedLevel < Level);
      Stable =  (laggedLevel = Level);
   end;

run;

Why are you creating three variables? Maybe creating one variable with three different values allows easier usage of the data.

[1SasUser1]

Thank you for responding. Your code works perfect for the most part. My only bit is when the level increases. So the part when it goes 1, 2, 3, 4, 5. Is there a way to just make it say 1 at the first increase, rather than the column Rolled having 1 next to each higher level?

	What it shows with your code	What I want it to show
Level	Rolled	Rolled
0	.	.
0	0	0
1	1	1
0	0	0
1	1	1
2	1	0
3	1	0
4	1	0
5	1	0

The reason why there are 3 separate columns is so that afterwards I can sum the number of rolls per person.

Thanks again.

[andreas_lds]

Should be possible, but i can't post tested code without data to work with, so please post data in usable form: a data step using datalines. See How to convert datasets to data steps for details.

[1SasUser1]

Thanks for that link, however I get the following error message: ERROR: The connection has timed out..
ERROR: Cannot open %INCLUDE file REPREX. ERROR 180-322: Statement is not valid or it is used out of proper order.

I have attached an Excel file, are you able to import that?

[andreas_lds]

---

@1SasUser1 wrote:

Thanks for that link, however I get the following error message: ERROR: The connection has timed out..
ERROR: Cannot open %INCLUDE file REPREX. ERROR 180-322: Statement is not valid or it is used out of proper order.

I have attached an Excel file, are you able to import that?

---

No, i can't open excel files.

[1SasUser1]

Hi,

I think I managed it:

data WORK.SAMPLEDATA;
infile datalines dsd truncover;
input Number:BEST12. Level:BEST12.;
format Number BEST12. Level BEST12.;
datalines;
12300000 0
12300000 0
12300000 1
12300000 0
12300000 1
12300000 2
12300000 3
12300000 4
12300000 5
45600000 0
45600000 0
45600000 0
45600000 1
45600000 0
45600000 0
45600000 0
45600000 0
45600000 0
45600000 0
45600000 0
45600000 0
78900000 0
78900000 0
78900000 1
78900000 0
78900000 1
78900000 0
78900000 1
78900000 1
78900000 2
78900000 3
78900000 3
78900000 4
;;;;

Hope this works. Thanks again.