topic array to look up value from the previous record in SAS Programming

array to look up value from the previous record

chayo — Mon, 01 Jul 2013 21:20:48 GMT

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

Re: array to look up value from the previous record

Haikuo — Mon, 01 Jul 2013 22:12:03 GMT

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

Re: array to look up value from the previous record

Ksharp — Tue, 02 Jul 2013 01:17:30 GMT

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

Re: array to look up value from the previous record

chayo — Tue, 02 Jul 2013 14:07:08 GMT

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