DATA Step, Macro, Functions and more

How to read data from next line?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How to read data from next line?

I have a dataset that looks like this:

 

Obs

ID

Indate

Outdate

Lines

1

1

17/08/10

20/08/10

1

2

1

17/08/10

22/08/10

2

3

2

06/01/11

10/01/11

1

4

3

15/08/10

15/08/10

1

5

3

15/08/10

17/08/10

2

6

3

15/08/10

25/08/10

3

7

3

15/08/10

21/09/10

4

8

4

17/06/12

17/06/12

1

9

5

26/01/11

27/01/11

1

10

6

06/11/12

06/11/12

1

11

6

14/12/12

14/12/12

1

12

6

17/11/14

17/11/14

1

13

6

17/11/14

18/11/14

1

14

7

30/10/10

31/10/10

1

15

7

30/10/10

01/11/10

2

 

And by using the statement:

 

proc sort data=have;

     by pnr indate;

run;

 

data have;

     set have; by pnr indate;

     LastLine=last.indate;

run;

 

I get this:

 

Obs

ID

Indate

Outdate

Lines

LastLine

1

1

17/08/10

20/08/10

1

0

2

1

17/08/10

22/08/10

2

1

3

2

06/01/11

10/01/11

1

1

4

3

15/08/10

15/08/10

1

0

5

3

15/08/10

17/08/10

2

0

6

3

15/08/10

25/08/10

3

0

7

3

15/08/10

21/09/10

4

1

8

4

17/06/12

17/06/12

1

1

9

5

26/01/11

27/01/11

1

1

10

6

06/11/12

06/11/12

1

1

11

6

14/12/12

14/12/12

1

1

12

6

17/11/14

17/11/14

1

0

13

6

17/11/14

18/11/14

1

1

14

7

30/10/10

31/10/10

1

0

15

7

30/10/10

01/11/10

2

1

 

It is correct until observation nr. 12. This should be 1 because Lines=1 in the next row... I've tried to sort by Outdate instead and I have used the lag(indate) but nothings works for me... How do I get it right? 


Accepted Solutions
Solution
‎04-02-2016 06:52 AM
Respected Advisor
Posts: 3,799

Re: How to read data from next line?

[ Edited ]

Use a view and create a new variable G(roup) that is incremented when lines eq 1.

 

data inout;
   infile cards dsd dlm='09'x firstobs=2;
   input id (indate outdate)(:ddmmyy.) lines;
   format indate outdate ddmmyy10.;
   cards;
ID	Indate	Outdate	Lines
1	17/08/10	20/08/10	1
1	17/08/10	22/08/10	2
2	6/1/2011	10/1/2011	1
3	15/08/10	15/08/10	1
3	15/08/10	17/08/10	2
3	15/08/10	25/08/10	3
3	15/08/10	21/09/10	4
4	17/06/12	17/06/12	1
5	26/01/11	27/01/11	1
6	6/11/2012	6/11/2012	1
6	14/12/12	14/12/12	1
6	17/11/14	17/11/14	1
6	17/11/14	18/11/14	1
7	30/10/10	31/10/10	1
7	30/10/10	1/11/2010	2
;;;;
   run;
proc print;
   run;
data inout2v / view=inout2v;
   set inout;
   by id;
   if first.id then g=0;
   if lines eq 1 then g+1;
   run;
data inout2;
   set inout2v;
   by id g;
   last = last.g;
   run;
proc print;
   run;

Capture.PNG

View solution in original post


All Replies
Super User
Super User
Posts: 7,950

Re: How to read data from next line?

Your code doesn't use lines at all it is just flagging the last indate.  Sort by all of the items in the group, and then use last.smallest group, for example (not tested as test data not as datastep):

proc sort data=have;
  by pnr indate lines;
run;
 
data have;
  set have; 
  by pnr indate lines;
  lastline=last.lines;
run;
Occasional Contributor
Posts: 15

Re: How to read data from next line?

I've already tried this and it dose not work: all the values of last.line =1...

Solution
‎04-02-2016 06:52 AM
Respected Advisor
Posts: 3,799

Re: How to read data from next line?

[ Edited ]

Use a view and create a new variable G(roup) that is incremented when lines eq 1.

 

data inout;
   infile cards dsd dlm='09'x firstobs=2;
   input id (indate outdate)(:ddmmyy.) lines;
   format indate outdate ddmmyy10.;
   cards;
ID	Indate	Outdate	Lines
1	17/08/10	20/08/10	1
1	17/08/10	22/08/10	2
2	6/1/2011	10/1/2011	1
3	15/08/10	15/08/10	1
3	15/08/10	17/08/10	2
3	15/08/10	25/08/10	3
3	15/08/10	21/09/10	4
4	17/06/12	17/06/12	1
5	26/01/11	27/01/11	1
6	6/11/2012	6/11/2012	1
6	14/12/12	14/12/12	1
6	17/11/14	17/11/14	1
6	17/11/14	18/11/14	1
7	30/10/10	31/10/10	1
7	30/10/10	1/11/2010	2
;;;;
   run;
proc print;
   run;
data inout2v / view=inout2v;
   set inout;
   by id;
   if first.id then g=0;
   if lines eq 1 then g+1;
   run;
data inout2;
   set inout2v;
   by id g;
   last = last.g;
   run;
proc print;
   run;

Capture.PNG

Occasional Contributor
Posts: 15

Re: How to read data from next line?

Posted in reply to data_null__

This datastep dosen't work:

data inout2;
   set inout2;
   by id g;
   last = last.g;
   run;
proc print;

I get: 

ERROR: BY variable pnr is not on input data set

ERROR: BY variable is not on input data set

Occasional Contributor
Posts: 15

Re: How to read data from next line?

I found out - it seems to work without the view-statement. Thanks a lot!

 

 

Respected Advisor
Posts: 3,799

Re: How to read data from next line?

I changed to the view after I had use a data set.  The perils of a continuing SAS session.  I'll edit my reply.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 435 views
  • 0 likes
  • 3 in conversation