Meteorite | Level 14

## calculation across observarions

Hello

In the following data set there are 6 rows for each ID.

I need to create a data set that contain 1 row for each customer and have information of #months that Y was lower than 3000.

Expected results:

for ID=1 we will get 0

for ID=1 we will get 1

for ID=1 we will get 4

What is the way to do it please?

``````Data tbl;
input ID Y;
cards;
1 3000
1 3400
1 3100
1 3200
1 3300
1 3300
2 3500
2 2900
2 3100
2 3100
2 3200
2 3300
3 3200
3 2900
3 2800
3 2800
3 2950
3 3200
;
run;
``````
5 REPLIES 5
Tourmaline | Level 20

## Re: calculation across observarions

``````proc sql;
create table want as
select ID, sum(Y<3000) as Ylower3000
from tbl
group by ID;
quit;``````

Result:

```ID  Ylower3000
1   0
2   1
3   4 ```

Meteorite | Level 14

## Re: calculation across observarions

What is the data set way to do it?

I know to do it via proc sql  but don't know via data set way

Tourmaline | Level 20

## Re: calculation across observarions

You can do it with a data step like this

``````data want(drop=Y);
do until (last.id);
set tbl;
by id;
if Y < 3000 then Ylower3000+1;
end;
output;
Ylower3000=0;
run;``````
Tourmaline | Level 20

## Re: calculation across observarions

Of course, this approach relies on the input data to be sorted by ID. If it is not, then you can do something like this

``````Data tbl;
input ID Y;
cards;
1 3000
1 3200
1 3300
1 3300
3 2800
3 2800
3 2950
3 3200
2 3500
2 2900
2 3100
2 3100
2 3200
1 3400
1 3100
2 3300
3 3200
3 2900
;
run;

data _null_;
if _N_=1 then do;
declare hash h(ordered:'Y');
h.definekey('ID');
h.definedata('ID', 'Ylower3000');
h.definedone();
end;

set tbl end=lr;

if h.check() ne 0 then do;
Ylower3000=0;
end;
if Y<3000 then do;
rc=h.find();
Ylower3000+1;
rc=h.replace();
end;

if lr then h.output(dataset:'want');
run;``````
Onyx | Level 15

## Re: calculation across observarions

Hi,

Just for fun. I think that direct addressing also will do in this case 🙂

Bart

``````Data tbl;
input ID Y;
cards;
1 3000
1 3200
1 3300
1 3300
3 2800
3 2800
3 2950
3 3200
2 3500
2 2900
2 3100
2 3100
2 3200
1 3400
1 3100
2 3300
3 3200
3 2900
5 3200
5 2900
;
run;

%let size = 10000;
data want;
array t[&size.] _temporary_;

min = &size.; max = 0;
do until(eof);
set tbl end=eof;
t[id] + (Y<3000);
min = min><id;
max = max<>id;
end;

do id = min to max;
Ylower3000 = t[id];
put _all_;
if Ylower3000 > .z then output;
end;
stop;
keep id Ylower3000;
run;``````

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

Discussion stats
• 5 replies
• 526 views
• 1 like
• 3 in conversation