in the following dataset i want to derive a new variable 'TYPE' in which the max and min of val2 within id for each val1 should be populated like
ID VAL1 VAL2
001 HT 170
001 HT 170
001 HT 170
001 WT 56
001 WT 78
001 WT 91
001 TM 32
001 TM 31
001 TM 27
001 BP 120
001 BP 111
001 BP 178
002 HT 169
002 HT 169
002 HT 169
002 WT 34
002 WT 89
002 WT 90
if the val2 is constant for any val2(here HT) I have to keep only one record for that .the output should be like;
ID VAL1 VAL2 TYPE
001 HT 170
001 WT 56 min
001 WT 78
001 WT 91 max
001 TM 32 max
001 TM 31
001 TM 27 min
001 BP 120
001 BP 111 min
001 BP 178 max
002 HT 169
002 WT 34 min
002 WT 89
002 WT 90 max
please help.
Does the order of the observations matter? Are we allowed to sort the data?
Does the following give you what you want:
data have;
input id $3.
val1 : $2.
val2 : 8.
;
datalines;
001 HT 170
001 HT 170
001 HT 170
001 WT 56
001 WT 78
001 WT 91
001 TM 32
001 TM 31
001 TM 27
001 BP 120
001 BP 111
001 BP 178
002 HT 169
002 HT 169
002 HT 169
002 WT 34
002 WT 89
002 WT 90
;
/* process using double DOW loop */
data want(drop = minimum maximum done_constant);
/* work out min and max for this val1 group */
do until(last.val1);
set have;
by id val1 notsorted;
minimum = min(minimum,val2);
maximum = max(maximum,val2);
end;
/* work out if this val1 record is constant, else assign 'type' */
do until(last.val1);
set have;
by id val1 notsorted;
if minimum = maximum then
do;
if not done_constant then
do;
output;
done_constant = 1;
end;
end;
else
do;
type = ifc(val2 = minimum,'min',ifc(val2 = maximum,'max',''));
output;
end;
end;
run;
Amir.
DATA want(drop=min);
set have end=last;
by id val1 notsorted;
retain min .;
if first.val1 then do;
type='min';
min=val2;
end;
if last.val1 then do;
type='max';
if min eq val2 then call execute('PROC SQL;UPDATE want SET type="" WHERE id eq "'||strip(id)||'" AND val1 eq "'||strip(val1)||'" AND val2 eq '||strip(put(val2,best.))||';QUIT;');
end;
RUN;
PROC SORT data=want nodupkey;by id val1 val2 type; RUN;
- Cheers -
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.