SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Extracting Only TWO groups

Reply
Occasional Contributor
Posts: 16

Extracting Only TWO groups

Hello. I have the following dataset 

 

IDvalueseq_id
1991
1952
1453
1664
15005
1556
2991
21002
2993
2554
2455
31011
32022
3443
3554

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"

IDvalueseq_idOutput
1991 
1952 
1453 
1664 
15005STOP
1556 
2991BLUE
21002 
2993 
2554 
2455 
31011Pending
32022 
3443 
3554 
Frequent Contributor
Posts: 120

Re: Extracting Only TWO groups

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;

Respected Advisor
Posts: 3,908

Re: Extracting Only TWO groups

@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;
Ask a Question
Discussion stats
  • 2 replies
  • 144 views
  • 0 likes
  • 3 in conversation