BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;
Required Output: subject visitnum results required 101 1 3.9 . 101 2 2.2 3.9 101 3 2.2 2.2 101 4 2.0 2.2 101 5 0.9 2.0 101 6 2.0 0.9 The logicis for subject we need to get minimum value before to the visitnum
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Using my previous code, with a little twist:

data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 1.1
101 3 2.2
101 4 2.0
101 5 0.9
101 5 0.9
;

data want;
set have;
by subject;
retain required;
if first.subject
then required = .;
output;
if first.subject
then required = min(results,9999); * use sufficiently large value here;
else required = min(results,required);
run;

proc print data=want noobs;
run;

Result:

subject    visitnum    results    required

  101          1         3.9          .   
  101          2         1.1         3.9  
  101          3         2.2         1.1  
  101          4         2.0         1.1  
  101          5         0.9         1.1  
  101          5         0.9         0.9  

View solution in original post

12 REPLIES 12
sivastat08
Pyrite | Level 9
Hi,

data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;

Data want;
set have;
required=lag(results);
run;
sivastat08
Pyrite | Level 9

Hello sir,

user might have

miss spelled instead of (101 1 1.1)=(101 1 3.9)

data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;

 

Kurt_Bremser
Super User

Use a retained variable, by-group processing, and the min() function:

(based on your example data)

data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
;

data want;
set have;
by subject;
retain required;
if first.subject
then required = min(results,9999); * use sufficiently large value here;
else required = min(results,required);
run;

 


@rajeshalwayswel wrote:
data have;
input subject visitnum results;
cards;
101 1 1.1
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
run;
Required Output: subject visitnum results required 101 1 3.9 . 101 2 2.2 3.9 101 3 2.2 2.2 101 4 2.0 2.2 101 5 0.9 2.0 101 6 2.0 0.9 The logicis for subject we need to get minimum value before to the visitnum

 

Ksharp
Super User

data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 2.2
101 3 2.2
101 4 2.0
101 5 0.9
101 6 2.0
;
run;
proc sql;
create table want as
select *,(select min(results) from have 
where subject=a.subject and visitnum < a.visitnum) as min
from have as a;
quit;
rajeshalwayswel
Pyrite | Level 9
Hi,

It's work for the some the records but when I haves same visitnum it not working, Could you please have a look on the below data.


data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 1.1
101 3 2.2
101 4 2.0
101 5 0.9
101 5 0.9
;
run;

Required:

101 1 3.9  .
101 2 1.1  3.9
101 3 2.2  1.1
101 4 2.0  1.1
101 5 0.9  1.1
101 5 0.9   0.9
Kurt_Bremser
Super User

Using my previous code, with a little twist:

data have;
input subject visitnum results;
cards;
101 1 3.9
101 2 1.1
101 3 2.2
101 4 2.0
101 5 0.9
101 5 0.9
;

data want;
set have;
by subject;
retain required;
if first.subject
then required = .;
output;
if first.subject
then required = min(results,9999); * use sufficiently large value here;
else required = min(results,required);
run;

proc print data=want noobs;
run;

Result:

subject    visitnum    results    required

  101          1         3.9          .   
  101          2         1.1         3.9  
  101          3         2.2         1.1  
  101          4         2.0         1.1  
  101          5         0.9         1.1  
  101          5         0.9         0.9  
rajeshalwayswel
Pyrite | Level 9
Thanks so much Kurtbremser. It's worked....
FreelanceReinh
Jade | Level 19

Hi @rajeshalwayswel,

 

I would use a slightly modified DATA step approach, for example one combining the suggestions from @Kurt_Bremser and @sivastat08:

data want;
set have;
by subject;
retain required 0;
required=min(required,lag(results));
if first.subject then required=.;
run;

Or, similarly, without the LAG function:

data want;
set have;
by subject;
retain required;
if first.subject then required=.;
output;
required=min(required,results);
run;

(Note: The purpose of the arbitrary initial value 0 for required in the first solution is just to avoid the note "Missing values were generated ..." for the very first observation.)

 

Both variants assume that dataset HAVE is sorted by subject and chronologically within each subject. The advantage of @Ksharp's solution is that it does not require HAVE to be sorted. It assumes that visits of a subject are numbered chronologically, though.

Tom
Super User Tom
Super User

Your data example works with a simple LAG() function call.

Perhaps you need to update your example where the min is not just the value of the previous observation?

 

 

data want;
  set have ;
  by subject ;
  want = min(want,lag(results));
  if first.subject then want=.;
  retain want;
run;

Here is slighly modified data demonstrates the difference.

 

 

data have;
input subject visitnum results required;
cards;
101       1        3.9    .
101       2        2.2    3.9
101       3        2.0    2.2
101       4        2.2    2.0
101       5        0.9    2.0
101       6        2.0    0.9
;
data want;
  set have ;
  by subject ;
  lag_results=lag(results);
  want = min(want,lag(results));
  if first.subject then call missing(want,lag_results);
  retain want;
run;

Notice the different result for VISTNUM=5?

                                                       lag_
Obs    subject    visitnum    results    required    results    want

 1       101          1         3.9          .          .         .
 2       101          2         2.2         3.9        3.9       3.9
 3       101          3         2.0         2.2        2.2       2.2
 4       101          4         2.2         2.0        2.0       2.0
 5       101          5         0.9         2.0        2.2       2.0
 6       101          6         2.0         0.9        0.9       0.9

 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 2440 views
  • 8 likes
  • 7 in conversation