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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.