topic Extracting Only TWO groups in SAS Data Management

Extracting Only TWO groups

avama — Thu, 13 Jul 2017 19:07:48 GMT

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

Re: Extracting Only TWO groups

FredrikE — Fri, 14 Jul 2017 12:51:30 GMT

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;

Re: Extracting Only TWO groups

Patrick — Sat, 15 Jul 2017 04:32:22 GMT

@avama

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;