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

Hierarchy - by constraints

Reply
Occasional Contributor
Posts: 16

Hierarchy - by constraints

[ Edited ]
 

Hello I have the following dataset but I am really stuck on how to do the hierarchy for few weeks actually. 

 

 
 
  • If there are TWO continuous 'HIGH' numbers, I would like to assign that group as high.
  • If not - I would like to see test if the group belongs to the next category which 'LOW' (again TWO continuous LOW).
  • If so, I would like to assign that group as "LOW". (Note: If there are two low and two highs continuous - I would like to assign it "HIGH" based on the hierarchy.
  • However if the GROUP has any values GREATER than 20 (regardless of once or twice occurence, the whole group is 'TOO HIGH'). Two continuous values does not apply to 'TOO HIGH' group. 
 

 Here is the dataset

IDvalueseq_id
131
112
123
144
115
2201
2212
253
311
362
373
314
345
3216
3177
318
329
461
492
4123
4134
511
562
573
5144
5205
6111
6122
6253
6164
6215

 

 

Here's the output I am looking for:

 

IDvalueseq_idoutput
131Normal
112 
123 
144 
115 
2201Too High
2212 
253 
311 
362 
373 
314 
345 
3216Too High
3177 
318 
329 
461 
492 
4123High
4134 
511 
562Low
573 
5144 
5195 
6111 
6122 
6253Too High
6164 
6215

 

 

Super User
Posts: 17,824

Re: Hierarchy - by constraints

We can't answer your question because it's not clear. Which variable matches the 'group' variable you mention? How do the other variables come Into play. 

Occasional Contributor
Posts: 16

Re: Hierarchy - by constraints

Hello. Group = ID. I would like to group them each ID. For example if group ID = 1, I would like to see if there are two consequtive 'too high' numbers. If no, I would like to see if there are two conesequitve 'high' numbers. If yes, then I would like to assign that 'high' to the first value of 'high'. If it is not HIGH, then I would like to see if there are two consequtives numbers that are 'low' category, and so on. 

 

There's always going to be two continouous values of group but I would like to start by too high, high, low, normal. 

 

Thank you!

Super User
Posts: 6,936

Re: Hierarchy - by constraints

[ Edited ]
data have;
infile cards dlm='09'x;
input ID value seq_id;
cards;
1	3	1
1	1	2
1	2	3
1	4	4
1	1	5
2	20	1
2	21	2
2	5	3
3	1	1
3	6	2
3	7	3
3	1	4
3	4	5
3	21	6
3	17	7
3	1	8
3	2	9
4	6	1
4	9	2
4	12	3
4	13	4
5	1	1
5	6	2
5	7	3
5	14	4
5	20	5
6	11	1
6	12	2
6	25	3
6	16	4
6	21	5
;
run;

proc format library=work;
value labelfmt
  1 = 'too high'
  2 = 'high'
  3 = 'low'
  4 = 'normal'
;
run;

data want (keep=id output);
set have;
by id;
retain flag;
if first.id then flag = '    ';
lagval = lag(value);
if value > 20 then substr(flag,1,1) = 'x';
else if not first.id
then do;
  if 16 <= value <= 20 and 16 <= lagval <= 20 then substr(flag,2,1) = 'x'; 
  else if 10 <= value <= 20 and 10 <= lagval <= 20 then substr(flag,3,1) = 'x';
  else substr(flag,4,1) = 'x';
end;
if last.id
then do;
  pos = index(flag,'x');
  output = put(pos,labelfmt.);
  output;
end;
run;

Note that in your example, group 4 was incorrectly labeled "high", as values 12 & 13 fall into your "low" category.

 

PS although I used your variable name "output", I recommend to not use SAS keywords as variable names.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 153 views
  • 0 likes
  • 3 in conversation