topic Using X Number of Latest Values for Calculations in SAS Programming

Using X Number of Latest Values for Calculations

wjugon85 — Mon, 12 Oct 2015 11:37:06 GMT

Hi all,

I have a general question for help on efficiency of task I am doing.

I have a dataset which has a set of scores for each student.

STUDENTID SCORE

1 5

1 4

1 3

1 2

1 5

2 1

2 2

2 3

2 1

3 4

What I want to do is create a new dataset which will contain the STUDYID, HIGHEST SCORE, AVERAGE SCORE. this is fine if using all data, however my requirements are to only use, the last 3 valid observations (i.e. not null) per student.

If less than 3 datapoints exists, then HIGHEST and AVERAGE are considered missing.

i.e.

STUDYID HIGHEST AVERAGE

1 5 3.33

2 3 2

3 . .

I am really just trying to find a way to select thos last 3 values per student to use.

Kind regards,

Re: Using X Number of Latest Values for Calculations

Astounding — Mon, 12 Oct 2015 13:18:04 GMT

You will need to process the data twice. One time counts the number of records, and the other calculates statistics. The easiest way might be to use a single DATA step:

data want;

n_records = 0;

do until (last.ID);

set have;

by ID;

where score > .;

n_records + 1;

end;

n_records2 = 0;

useful_records = min(3, n_records);

do until (last.ID);

set have;

by ID;

where score > .;

n_records2 + 1;

if (useful_records >= 3) and (n_records2 >= n_records-2) then do;

highest = max(highest, score);

average = sum(average, score);

end;

if average > . then average = average/3;

drop n_records n_records2;

run;

The first DO loop counts, and the second calculates.

Re: Using X Number of Latest Values for Calculations

MikeZdeb — Mon, 12 Oct 2015 14:55:18 GMT

Hi, this works with your data ...

data x;
input studentid score @@;
datalines;
1 5 1 4 1 4 1 3 1 3 1 2 1 5 2 1
2 1 2 1 2 2 2 3 2 1 3 4 3 4
;

data y (keep=studentid average highest);
array z(100);
do j=1 by 1 until(last.studentid);
   set x;
   by studentid;
   z(j) = score;
end;
if j ge 3 then do;
   highest = max(of z(j) z(j-1) z(j-2));
   average = round(mean(of z(j) z(j-1) z(j-2)),.01);;
end;
run;

data set Y ...

Obs studentid highest average

1         1           5         3.33
2         2           3         2.00
3         3           .          .

Re: Using X Number of Latest Values for Calculations

Astounding — Mon, 12 Oct 2015 15:28:56 GMT

Much simpler. Here's a wrinkle that removes any worries about the number of incoming scores:

array z {0:2} z0-z2;

Then:

z{mod(j,3)} = score;

Then compute max and average based on z{*}.

Re: Using X Number of Latest Values for Calculations

MikeZdeb — Mon, 12 Oct 2015 17:37:08 GMT

Hi, OK but no need to bother with the z(0:2) part ...

data y (keep=studentid average highest);
array z(3);
do j=1 by 1 until(last.studentid);
   set x;
   by studentid;
   z(mod(j,3)+1) = score;

end;

if j ge 3 then do;
highest = max(of z(*));
average = round(mean(of z(*)),.01);;
end;
run;

Re: Using X Number of Latest Values for Calculations

PGStats — Mon, 12 Oct 2015 17:45:10 GMT

The simpler the better


data have;
input StudentId score @@;
datalines;
1  5 1  4 1  4 1  3 1  3 1  2 1  5 2  1
2  1 2  1 2  2 2  3 2  1 3  4 3  4 
;

data want;
do until(last.studentId);
    set have; by studentId;
    where score is not missing;
    v2 = v1;
    v1 = v0;
    v0 = score;
    end;
if not missing(v2) then do;
    Highest = max(of v:);
    Average = mean(of v:);
    end;
keep studentId average highest;
format average 4.1;
run;

proc print data=want noobs; run;

thanks @MikeZdeb