Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Select the least value in the repeated measurements while marking the ...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 10-17-2018 12:48 PM
(1255 views)

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
;
```

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

15 REPLIES 15

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Do you just have 2 vars to check or there could be many?

Very interesting question indeed as missing killed the min lol 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

" However, it can be many in the future" Hmm

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hmm, but two right now 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hey how is id 4 both?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

Thanks,

Suryakiran

Suryakiran

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks. WANT data is edited now. Sorry for a confusion.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I have to learn more about this vname function. Appears to be very helpful one. Right?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

How to Concatenate Values

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.