Hi ya'll,
I have number of individuals with repeated measurements with values of proc_range and dx_range. I'm interested in knowing which range variable has the least value across all measurements among these two variables proc_range and dx_range and mark which one was selected as the least in value. The problem here is also a missing in range variables. Because missing here is what its is and I would just go ahead with the variable with value available.
Thanks for any help. I appreciate your time!
data have;
input id proc_range dx_range;
cards;
1 12 9
1 8 12
1 6 8
1 6 8
1 5 14
1 5 11
1 5 8
1 5 .
1 5 13
2 7 0
2 8 0
2 14 0
2 13 0
2 11 0
3 8 7
3 0 .
3 7 7
4 1 5
4 1 .
4 1 7
;
data want;
input id range source;
cards;
1 5 proc
2 0 dx
3 0 proc
4 1 proc
;
Data sorted by ID? Eschew proc sql:
data have;
input id proc_range dx_range;
cards;
1 12 9
1 8 12
1 6 8
1 6 8
1 5 14
1 5 11
1 5 8
1 5 .
1 5 13
2 7 0
2 8 0
2 14 0
2 13 0
2 11 0
3 8 7
3 0 .
3 7 7
4 1 5
4 1 .
4 1 7
;
data want (keep=id min source);
set have;
by id;
array mins {2} _temporary_;
if first.id then call missing(of mins{*});
array src {2} $5 _temporary_ ('proc','dx');
array vals {2} proc_range dx_range;
do i=1 to 2;
mins{i}=min(mins{i},vals{i});
end;
if last.id;
min=min(of mins{*});
if mins{1}=mins{2} then source='both';
else source=src{whichn(min,of mins{*})};
run;
Do you just have 2 vars to check or there could be many?
Very interesting question indeed as missing killed the min lol 🙂
Yes, missing always makes our life more challenging and the problems more interesting? hehe. Thank god just two variables here. However, it can be many in the future. But two for the problem so far.
" However, it can be many in the future" Hmm
Hey how is id 4 both?
@novinosrin@data_null__@SuryaKiran
guys, thanks you so much. WANT data is just to give an idea what I'm trying to achieve.
I need to mark the minimum of proc_range and dx_range and if proc_range and dx_range contributes the same value then mark as 'both' contributed to the minimum value.
as simple as this, until it hits missing.
data WANT is the information I'm looking for not that final dataset has to look like exactly.
data have;
input id proc_range dx_range;
cards;
1 12 9
1 8 12
1 6 8
1 6 8
1 5 14
1 5 11
1 5 8
1 5 .
1 5 13
2 7 0
2 8 0
2 14 0
2 13 0
2 11 0
3 8 7
3 0 .
3 7 7
4 1 5
4 1 .
4 1 7
;
proc sql;
create table temp as
select ID, min(proc_range) as min_proc,min(dx_range) as min_dx
from have
group by id;
quit;
data want;
merge have temp;
by id;
length source $32;
retain source;
array j(*) proc_range dx_range;
R=min(min_proc,min_dx);
if min_proc=min_dx then source='BOTH';
else do;
_iorc_=whichn(R,proc_range,dx_range);
if _iorc_>0 then source=vname(j(_iorc_));
end;
if last.id;
keep id source r;
run;
If ignoring missing then how did you get both for the last record?
You might need to change the expression in IFN and IFC to meet your requirements.
proc sql;
select pr.id, ifn(proc<dx,proc,dx) as range, /* You can change the expression here */
ifc(proc<dx,"proc","dx") as source
from (select id,min(Proc_range) as Proc
from have
where Proc_range is not null
group by id) as pr
left join (select id,min(dx_range) as dx
from have
where dx_range is not null
group by id
) as dx
on pr.id=dx.id;
quit;
Since you need source information Proc sql suggestion is actually out of scope for the reason vname is not supported in proc sql nor is array processing when I believe the complexity will eventually arise as the number of vars increases
Does this produce the an intermediate to your want data. I don't really understand your WANT data.
1365 ;;;;
1366 run;
1367 proc print;
1368 run;
The SAS System 06:53 Wednesday, October 17, 2018 130
proc_
Obs id range dx_range
1 1 12 9
2 1 8 12
3 1 6 8
4 1 6 8
5 1 5 14
6 1 5 11
7 1 5 8
8 1 5 .
9 1 5 13
10 2 7 0
11 2 8 0
12 2 14 0
13 2 13 0
14 2 11 0
15 3 8 7
16 3 0 .
17 3 7 7
18 4 1 5
19 4 1 .
20 4 1 7
NOTE: There were 20 observations read from the data set WORK.HAVE.
1369 proc summary nway missing;
1370 class id;
1371 output out=test
1372 idgroup(min(proc_range) missing out(proc_range)=)
1373 idgroup(min(dx_range) missing out(dx_range)=)
1374 ;
1375 run;
NOTE: There were 20 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.TEST has 4 observations and 5 variables.
1376 proc print;
1377 run;
The SAS System 06:53 Wednesday, October 17, 2018 131
proc_
Obs id _TYPE_ _FREQ_ range dx_range
1 1 1 9 5 .
2 2 1 5 7 0
3 3 1 3 0 .
4 4 1 3 1 .
NOTE: There were 4 observations read from the data set WORK.TEST.
1378 proc printto;
1379 run;
Data sorted by ID? Eschew proc sql:
data have;
input id proc_range dx_range;
cards;
1 12 9
1 8 12
1 6 8
1 6 8
1 5 14
1 5 11
1 5 8
1 5 .
1 5 13
2 7 0
2 8 0
2 14 0
2 13 0
2 11 0
3 8 7
3 0 .
3 7 7
4 1 5
4 1 .
4 1 7
;
data want (keep=id min source);
set have;
by id;
array mins {2} _temporary_;
if first.id then call missing(of mins{*});
array src {2} $5 _temporary_ ('proc','dx');
array vals {2} proc_range dx_range;
do i=1 to 2;
mins{i}=min(mins{i},vals{i});
end;
if last.id;
min=min(of mins{*});
if mins{1}=mins{2} then source='both';
else source=src{whichn(min,of mins{*})};
run;
data have;
input id proc_range dx_range;
cards;
1 12 9
1 8 12
1 6 8
1 6 8
1 5 14
1 5 11
1 5 8
1 5 .
1 5 13
2 7 0
2 8 0
2 14 0
2 13 0
2 11 0
3 8 7
3 0 .
3 7 7
4 1 5
4 1 .
4 1 7
;
PROC MEANS DATA=have NOPRINT NWAY;
CLASS id;
VAR proc_range--dx_range;
OUTPUT OUT=temp(drop=_:) min=;
RUN;
data want;
set temp;
array t(*) proc_range--dx_range;
array j(*) proc_range--dx_range min;
length source $20;
min=min(of t(*));
if range(of j(*))=0 then source='BOTH';
else do;
_t=whichn(min,of t(*));
if _t>0 then source=vname(t(_t));
end;
keep id min source ;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.