topic Re: Using proc sql to create min and max in SAS Programming

Using proc sql to create min and max

Dregerator — Fri, 01 May 2020 00:37:07 GMT

Hi,

I'm trying to create a new variable that indicates a minimum value and a maximum value of a parameter by subjid.

Basically trying to follow this specification:

Per SUBJID & PARAM:
MAXIMUM = the first record with the largest AVAL
MINIMUM = the first record with the smallest AVAL

However, i'm having trouble with my proc sql syntax

proc sql;
create table lb2 as
select subjid, param, aval,
min(AVAL) as DTYPE="Max",
max(AVAL) as DTYPE="Min",
from lb
group by subjid param;
quit;

Thank you in advance.

Re: Using proc sql to create min and max

novinosrin — Fri, 01 May 2020 01:30:57 GMT

Hi @Dregerator I can only spot some minor syntax error in your code

proc sql;
create table lb2 as
select subjid, param, 
min(AVAL) as DTYPE_min label="Max",
max(AVAL) as DTYPE_max label="Min"
from lb
group by subjid, param;
quit;

Re: Using proc sql to create min and max

Reeza — Fri, 01 May 2020 01:59:26 GMT

What happens if you have a tie for the min or max?

@Dregerator wrote:

Hi,

I'm trying to create a new variable that indicates a minimum value and a maximum value of a parameter by subjid.

Basically trying to follow this specification:

Per SUBJID & PARAM:
MAXIMUM = the first record with the largest AVAL
MINIMUM = the first record with the smallest AVAL

However, i'm having trouble with my proc sql syntax

proc sql;
create table lb2 as
select subjid, param, aval,
min(AVAL) as DTYPE="Max",
max(AVAL) as DTYPE="Min",
from lb
group by subjid param;
quit;

Thank you in advance.

Re: Using proc sql to create min and max

Dregerator — Fri, 01 May 2020 02:11:32 GMT

Hi,
So I actually want this

For example lets say i have this set of data:

subjid paramcd aval
1 ALT 34
1 ALT 39
1 ALT 28
1 ALT 28
1 ALT 40
1 ALT 40
1 APT 15
1 APT 15
1 APT 15
1 APT 9
1 APT 9
1 APT 15
1 APT 12

I want the variable DTYPE to indicate the first max value and the first min value:
subjid paramcd aval dtype
1 ALT 34
1 ALT 39
1 ALT 28 min
1 ALT 28
1 ALT 40 max
1 ALT 40
1 APT 15 max
1 APT 15
1 APT 15
1 APT 9 min
1 APT 9
1 APT 15
1 APT 12

Re: Using proc sql to create min and max

novinosrin — Fri, 01 May 2020 03:03:05 GMT

Hi @Dregerator Proc SQL is ill suited for this kind of problem other than the fact one may deem this as a fun puzzle to solve at 11pm eastern. Elders like @Reeza are likely to offer better and appropriate advice,



data have;
input subjid paramcd $ aval;
cards;
1 ALT 34
1 ALT 39
1 ALT 28
1 ALT 28
1 ALT 40
1 ALT 40
1 APT 15
1 APT 15
1 APT 15
1 APT 9
1 APT 9
1 APT 15
1 APT 12
;

proc sql;
create table want(drop=rn min max) as
select * , case when min(ifn(min=aval,rn,.))=rn then 'min'
when min(ifn(max=aval,rn,.))=rn	then 'max' else ' ' end as dtype
from
(select *, monotonic() as rn ,min(aval) as min, max(aval) as max
from have
group by subjid,paramcd) 
group by subjid, paramcd
order by rn;
quit;

proc print noobs;run;

Re: Using proc sql to create min and max

Kurt_Bremser — Fri, 01 May 2020 07:17:34 GMT

The proper tool is, as most often in SAS data preparation, the DATA step:

data want;
minval = 1e200;
maxval = .;
do until (last.paramcd);
  set have;
  by subjid paramcd;
  maxval = max(maxval,aval);
  minval = min(minval,aval);
end;
do until (last.paramcd);
  set have;
  by subjid paramcd;
  if aval = maxval
  then do;
    dtype = "max";
    maxval = .;
  end;
  if aval = minval
  then do;
    dtype = "min";
    minval = .;
  end;
  output;
  dtype = "";
end;
drop minval maxval;
run;

Although it has two set statements, physically it will do a single sequential pass through the dataset, with no remerges needed.

Re: Using proc sql to create min and max

ed_sas_member — Sat, 02 May 2020 14:24:11 GMT

Hi @Dregerator

You could also do this, and then sort your data according to CDISC guidelines (e.g. for BDS:

USUBJID, PARAMCD, AVISIT)

proc sort data=have;
	by subjid paramcd aval;
run;

data have;
	set have;
	by subjid paramcd;
	if first.paramcd then dtype="Min";
	if last.paramcd then dtype="Max";
run;

Re: Using proc sql to create min and max

Dregerator — Mon, 04 May 2020 21:52:36 GMT

Hi thank you this is useful, however, I'm trying to add on a specific condition without subsetting. I'm trying to add the condition LBNAM="CVC" basically out of the whole dataset I only what this min and max to occur where LBNAM="CVC" without reducing the number of observations in the whole dataset.

I tried doing the following but it doesn't work, as it keeps applying values to other LBNAMs

data want;
minval = 1e200;
maxval = .;
do until (last.paramcd);
set have;
if LBNAM = "CVC" then do;
by usubjid paramcd;
maxval = max(maxval,aval);
minval = min(minval,aval);
end;
end;
do until (last.paramcd);
set have;
by usubjid paramcd;
if aval = maxval
dtype = "max";
maxval = .;
end;
if aval = minval
then do;
dtype = "min";
minval = .;
end;
output;
dtype = "";
end;
end;
drop minval maxval;
run;

Re: Using proc sql to create min and max

Dregerator — Mon, 04 May 2020 21:54:37 GMT

Hi, is there a way to add a condition in this proc sql syntax to only do this when LBNAM="CVC" by any chance, without subsetting the whole dataset. Basically, I want it to perform this calculation only where LBNAM="CVC" out of all the 10 LBNAM's.

Thank you in advance.

Re: Using proc sql to create min and max

novinosrin — Mon, 04 May 2020 22:48:29 GMT

Hi @Dregerator Sure I can try, albeit I don't see any variable by the name LBNAM and itsvalues in the sample provided by you. Can you please post a modified sample and the corresponding expected output sample so that I have something to refer to. Thanks!

Re: Using proc sql to create min and max

Kurt_Bremser — Tue, 05 May 2020 06:00:40 GMT

Use the "little running man" icon to post code, otherwise we only see that ugly spaghetti code without any formatting
adapt the code like this:

data want;
minval = 1e200;
maxval = .;
do until (last.paramcd);
  set have;
  by subjid paramcd;
  if lbnam = 'CVC'
  then do;
    maxval = max(maxval,aval);
    minval = min(minval,aval);
  end;
end;
do until (last.paramcd);
  set have;
  by subjid paramcd;
  if lbname = 'CVC'
  then do;
    if aval = maxval
    then do;
      dtype = "max";
      maxval = .;
    end;
    if aval = minval
    then do;
      dtype = "min";
      minval = .;
    end;
  end;
  output;
  dtype = "";
end;
drop minval maxval;
run;

Re: Using proc sql to create min and max

Dregerator — Tue, 05 May 2020 18:06:52 GMT

Hi I figured it , I subsetted the data into two groups. One with LBNAM="CVC" and one group without it. Then performed the calculation via your code below, then set those subjects back with the group without the LBNAM="CVC" . However, I was hoping if your proc sql could be revised to also put Maximum as well if the aval is the same for example:

subjid paramcd aval
1 ALT 34
1 ALT 39
1 ALT 28
1 ALT 28
1 ALT 40
1 ALT 40
1 APT 15
1 APT 15
1 APT 15
1 APT 9
1 APT 9
1 APT 15
1 APT 12

1 ABC 9

1 ABC 9

I want the variable DTYPE to indicate the first max value and the first min value:
subjid paramcd aval dtype
1 ALT 34
1 ALT 39
1 ALT 28 min
1 ALT 28
1 ALT 40 max
1 ALT 40
1 APT 15 max
1 APT 15
1 APT 15
1 APT 9 min
1 APT 9
1 APT 15
1 APT 12

1 ABC 9 min

1 ABC 9 max

1 ABC 9

When aval values are same, then MIN and MAX should have same value.

Re: Using proc sql to create min and max

novinosrin — Tue, 05 May 2020 21:11:11 GMT

HI @Dregerator Please find the below modified code that should meet your additional requirement. While I enjoy crazy solutions sometimes that would generally be deemed not recommended, by all means give it a thought to go for a rather straight forward Datastep solution offered by others. Lately, I am getting increasingly bored with the predictable datastep solutions and so me being crazy likes to experiment crazy solutions for fun. Though against ethics as it may sound, admittedly I like some entertainment.

And please do relax, the contributors here in this community practice SAS religiously and do stick to the same thread and by all means knock the doors for help in the same thread. Trust me, You will get your solution. Take care and have fun!



data have;
input subjid paramcd $ aval;
cards;
1 ALT 34
1 ALT 39
1 ALT 28
1 ALT 28
1 ALT 40
1 ALT 40
1 APT 15
1 APT 15
1 APT 15
1 APT 9
1 APT 9
1 APT 15
1 APT 12
1 ABC 9
1 ABC 9
1 ABC 9
1 ABC 9
;


proc sql;
create table want(drop=rn min max) as
select * , case when min(ifn(min=aval and min ne max,rn,.))=rn then 'min'
when min(ifn(max=aval and min ne max,rn,.))=rn	then 'max' 
when min(ifn(min=aval and  min=max,rn,.))=rn then 'min'
when min(ifn(min=aval and  min=max,rn,.))+1=rn then 'max'
else ' ' end as dtype
from
(select *, monotonic() as rn ,min(aval) as min, max(aval) as max
from have
group by subjid,paramcd) 
group by subjid, paramcd
order by rn;
quit;

subjid paramcd aval dtype 
1 ALT 34   
1 ALT 39   
1 ALT 28 min 
1 ALT 28   
1 ALT 40 max 
1 ALT 40   
1 APT 15 max 
1 APT 15   
1 APT 15   
1 APT 9 min 
1 APT 9   
1 APT 15   
1 APT 12   
1 ABC 9 min 
1 ABC 9 max 
1 ABC 9   
1 ABC 9

Re: Using proc sql to create min and max

ghosh — Tue, 05 May 2020 22:11:48 GMT

Why not use Proc summary?

Proc summary nway data=lb; 
class subjid param;
var aval;
output out=lb2
 min=Min
 max=Max
;
run;