Hello. I have the following dataset
ID | value | seq_id |
1 | 99 | 1 |
1 | 95 | 2 |
1 | 45 | 3 |
1 | 66 | 4 |
1 | 500 | 5 |
1 | 55 | 6 |
2 | 99 | 1 |
2 | 100 | 2 |
2 | 99 | 3 |
2 | 55 | 4 |
2 | 45 | 5 |
3 | 101 | 1 |
3 | 202 | 2 |
3 | 44 | 3 |
3 | 55 | 4 |
What I would like to do is that
1. If a certain ID group has ALL the values <=100 then I would like to create a separate dataset for that and call it "BLUE" or even just "output column"
2. If a certain ID group has ANY ONE value that is >400 then I would like to create a seperate dataset for that and call it "STOP"
3. If they don't belong in that group then extract only that dataset into "PENDING"
ID | value | seq_id | Output |
1 | 99 | 1 | |
1 | 95 | 2 | |
1 | 45 | 3 | |
1 | 66 | 4 | |
1 | 500 | 5 | STOP |
1 | 55 | 6 | |
2 | 99 | 1 | BLUE |
2 | 100 | 2 | |
2 | 99 | 3 | |
2 | 55 | 4 | |
2 | 45 | 5 | |
3 | 101 | 1 | Pending |
3 | 202 | 2 | |
3 | 44 | 3 | |
3 | 55 | 4 |
How about this?
data test;
length ID value seq_id 8;
input ID value seq_id;
datalines;
1 99 1
1 95 2
1 45 3
1 66 4
1 500 5
1 55 6
2 99 1
2 100 2
2 99 3
2 55 4
2 45 5
3 101 1
3 202 2
3 44 3
3 55 4
;
run;
proc sql;
create table test2 as
select id, value, seq_id, max(value) as max
from test
group by id
;
quit;
data blue stop pending;
set test2;
if max > 400 then output stop;
else if max <= 100 then output blue;
else output pending;
run;
Instead of creating multiple data sets, you could also create a grouping variable.
data have;
length ID value seq_id 8;
input ID value seq_id;
datalines;
1 99 1
1 95 2
1 45 3
1 66 4
1 500 5
1 55 6
2 99 1
2 100 2
2 99 3
2 55 4
2 45 5
3 101 1
3 202 2
3 44 3
3 55 4
;
run;
proc sql;
create table want as
select
h.*,
case
when max(h.value)>400 then 'Stop'
when max(h.value)<=100 then 'BLUE'
else 'PENDING'
end as group length=10
from have as h
group by h.id
order by h.id, h.seq_id
;
quit;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.