BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Hima
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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 in:);
output;
end;
format maxpull date8.;
run;

View solution in original post

5 REPLIES 5
ballardw
Super User

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 indate:) 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.

stat_sas
Ammonite | Level 13

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 in:);
output;
end;
format maxpull date8.;
run;

Hima
Obsidian | Level 7

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 IN:);

MINPULL=MIN(OF IN:);

OUTPUT;

END;

FORMAT MAXPULL MINPULL DATE9.;

RUN;

data_null__
Jade | Level 19

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 in:);
      min=min(of in:);
     
end;
  
format max min date11.;
  
run;
proc print;
  
run;
8-14-2014 5-03-14 PM.png
Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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