Help using Base SAS procedures

Min and Max function on sequential arguments

Accepted Solution Solved
Reply
Regular Contributor
Posts: 233
Accepted Solution

Min and Max function on sequential arguments

The variables INDATE<n> are driven by the count on the variable open_date. In the below example, the count on the variable open_date is 5 so the INDATE would be from INDATE1 to INDATE5. I want to apply minimum and maximum function between the variable INDATE1 - INDATE5.

I can write it manually min(INDATE1,INDATE2,INDATE3,INDATE4,INDATE5) which serves the purpose but I want to enhance it in such a way that the code doesnt need to be modified manually every time the process runs.

Any help is greatly appreciated.Have
OPEN_DATECOUNTINDATE1INDATE2INDATE3INDATE4INDATE5
7-Aug-142667-Aug-14....
8-Aug-14480.8-Aug-14...
11-Aug-14269..11-Aug-14..
12-Aug-14435...12-Aug-14.
13-Aug-14281....13-Aug-14

Want

OPEN_DATECOUNTINDATE1INDATE2INDATE3INDATE4INDATE5MINPULLMAXPULLCNT
13-Aug-142817-Aug-148-Aug-1411-Aug-1412-Aug-1413-Aug-147-Aug-1413-Aug-141731

CNT is total of countvariable in have data set.


Accepted Solutions
Solution
‎08-14-2014 05:16 PM
Trusted Advisor
Posts: 1,204

Re: Min and Max function on sequential arguments

data have;
input OPEN_DATE : date7. COUNT (INDATE1 INDATE2 INDATE3 INDATE4 INDATE5)(: date7.);
format OPEN_DATE INDATE1 INDATE2 INDATE3 INDATE4 INDATE5 date7.;
datalines;
07-Aug-14 266 07-Aug-14 . . . .
08-Aug-14 480 . 08-Aug-14 . . .
11-Aug-14 269 . . 11-Aug-14 . .
12-Aug-14 435 . . . 12-Aug-14 .
13-Aug-14 281 . . . . 13-Aug-14
;

proc stdize data=have reponly method=mean out=want;
run;

data final;
set want end=last;
cnt+count;
if last then do;
maxpull=max(of inSmiley Happy;
output;
end;
format maxpull date8.;
run;

View solution in original post


All Replies
Super User
Posts: 10,466

Re: Min function on sequential arguments

Look up the idea of variable lists. Short hand if  you have a common stem for the names of variables is to use a colon following the common part of the variable

min ( of indateSmiley Happy for example.

Warning this will attempt to use all variables that start with INDATE and ALL of them must be of the same type (numeric in the case of min) to get expected results. If you were to have a character variable INDATE_comment that had text the above would generate and error.

Solution
‎08-14-2014 05:16 PM
Trusted Advisor
Posts: 1,204

Re: Min and Max function on sequential arguments

data have;
input OPEN_DATE : date7. COUNT (INDATE1 INDATE2 INDATE3 INDATE4 INDATE5)(: date7.);
format OPEN_DATE INDATE1 INDATE2 INDATE3 INDATE4 INDATE5 date7.;
datalines;
07-Aug-14 266 07-Aug-14 . . . .
08-Aug-14 480 . 08-Aug-14 . . .
11-Aug-14 269 . . 11-Aug-14 . .
12-Aug-14 435 . . . 12-Aug-14 .
13-Aug-14 281 . . . . 13-Aug-14
;

proc stdize data=have reponly method=mean out=want;
run;

data final;
set want end=last;
cnt+count;
if last then do;
maxpull=max(of inSmiley Happy;
output;
end;
format maxpull date8.;
run;

Regular Contributor
Posts: 233

Re: Min and Max function on sequential arguments

Thank you so much. It is a huge help. Just an FYI. As I need min value I added one more line to what you gave me.

PROC STDIZE DATA=IN1.PROC_DT1 REPONLY METHOD=MEAN OUT=IN1.PROC_DT2;

RUN;

DATA IN1.PROC_DT3;

SET IN1.PROC_DT2 END=LAST;

CNT+COUNT;

IF LAST THEN DO;

MAXPULL=MAX(OF INSmiley Happy;

MINPULL=MIN(OF INSmiley Happy;

OUTPUT;

END;

FORMAT MAXPULL MINPULL DATE9.;

RUN;

Respected Advisor
Posts: 3,777

Re: Min and Max function on sequential arguments

The UPDATE statement makes a more natural "flatten-er" for this scenario.  You do need a BY variable but I consider that a minor inconvenience.

data date;
   infile cards expandtabs;
  
input OPEN_DATE:Date11.   COUNT (INDATE1-INDATE5)(:date11.);
   retain id 1;
  
format o: in: date11.;
  
cards;
7-Aug-14 266   7-Aug-14 .  .  .  .
8-Aug-14 480   .  8-Aug-14 .  .  .
11-Aug-14   269   .  .  11-Aug-14   .  .
12-Aug-14   435   .  .  .  12-Aug-14   .
13-Aug-14   281   .  .  .  .  13-Aug-14
;;;;
   run;
data date2;
   update date(obs=0) date;
   by id;
   cnt + count;
  
if last.id then do;
      max=max(of inSmiley Happy;
      min=min(of inSmiley Happy;
     
end;
  
format max min date11.;
  
run;
proc print;
  
run;
8-14-2014 5-03-14 PM.png
Super User
Posts: 9,671

Re: Min and Max function on sequential arguments


data date;
   infile cards expandtabs; 
   input OPEN_DATE:Date11.   COUNT (INDATE1-INDATE5)(:date11.);
   format o: in: date11.; 
   cards; 
7-Aug-14 266   7-Aug-14 .  .  .  .
8-Aug-14 480   .  8-Aug-14 .  .  .
11-Aug-14   269   .  .  11-Aug-14   .  .
12-Aug-14   435   .  .  .  12-Aug-14   .
13-Aug-14   281   .  .  .  .  13-Aug-14
;;;;
proc summary data=date;
var _numeric_;
output out=sum(drop=_: OPEN_DATE rename=(count=cnt)) sum=;
run;
data want;
 set date point=nobs nobs=nobs ;
 set sum;
run;

Xia Keshan

Message was edited by: xia keshan

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 238 views
  • 7 likes
  • 5 in conversation