DATA Step, Macro, Functions and more

array to look up value from the previous record

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

array to look up value from the previous record

hi,

i created this output below using couple datasteps with arrays.  I'm struggling to do the following: return the ID number where the value increases month-over-month. In the example given below the output would be ID 16 (since in april12 it had gone up to 114 from 100)

I'm writing this in VBA without any issues, since the code loop through cells and compare the values from the previous record using index functions. I'm struggling transitioning this to SAS. any help is much appreciated. 

ID  Month Score

16 Jan12 112

16 Feb12 110

16 March12 100

16 April12 114

35 Jan12 120

35 Feb12 115

35 March 106

35 April 100


Accepted Solutions
Solution
‎07-01-2013 09:17 PM
Super User
Posts: 10,035

Re: array to look up value from the previous record

data have;
input ID $ Month $ Score;
cards;
16 Jan12 112
16 Feb12 110
16 March12 100
16 April12 114
35 Jan12 120
35 Feb12 115
35 March 106
35 April 100
;
run;
data want;
 set have;
 if id eq lag(id) and dif(score) gt 0;
run;

Ksharp

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: array to look up value from the previous record

If your data comes as is, lag() or dif() will be sufficient:

data have;

input ID $ Month $ Score;

cards;

16 Jan12 112

16 Feb12 110

16 March12 100

16 April12 114

35 Jan12 120

35 Feb12 115

35 March 106

35 April 100

;

data want;

  set have;

  by id notsorted;

d=dif(score);

  if not first.id and d>0;

keep id;

run;

Haikuo

Solution
‎07-01-2013 09:17 PM
Super User
Posts: 10,035

Re: array to look up value from the previous record

data have;
input ID $ Month $ Score;
cards;
16 Jan12 112
16 Feb12 110
16 March12 100
16 April12 114
35 Jan12 120
35 Feb12 115
35 March 106
35 April 100
;
run;
data want;
 set have;
 if id eq lag(id) and dif(score) gt 0;
run;

Ksharp

Occasional Contributor
Posts: 6

Re: array to look up value from the previous record

thank you Hai and ksharp for the help. it worked great!

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 169 views
  • 0 likes
  • 3 in conversation