33 data have; 34 input contype$1-3 TimeStamp CP PP MM; 35 informat TimeStamp date9.; 36 format TimeStamp date9.; 37 cards; NOTE: The data set WORK.HAVE has 8 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 46 ; 47 run; 48 49 proc sort data=have; 50 by contype timestamp; 51 run; NOTE: There were 8 observations read from the data set WORK.HAVE. NOTE: The data set WORK.HAVE has 8 observations and 5 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 53 data dates; 54 input enddate date9.; 55 informat enddate date9.; 56 format enddate date9.; 57 cards; NOTE: The data set WORK.DATES has 17 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 75 ; 76 run; 77 78 proc sort data=dates; 79 by enddate; 80 run; NOTE: There were 17 observations read from the data set WORK.DATES. NOTE: The data set WORK.DATES has 17 observations and 1 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 81 82 proc summary data=dates ; 83 var enddate; 84 output out=lastday max=enddate; 85 run; NOTE: There were 17 observations read from the data set WORK.DATES. NOTE: The data set WORK.LASTDAY has 1 observations and 3 variables. NOTE: PROCEDURE SUMMARY used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 86 87 data want; 88 if _n_=1 then set lastday(keep=enddate); 89 set have; 90 by contype; 91 output; 92 if last.contype then do; 93 cp=0; pp=0; mm=0; 94 do timestamp=timestamp+1 to enddate; 95 output; 96 end; 97 end; 98 run; NOTE: There were 1 observations read from the data set WORK.LASTDAY. NOTE: There were 8 observations read from the data set WORK.HAVE. NOTE: The data set WORK.WANT has 3477 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 99 100 proc sql; 101 create table test as 102 select a.* 103 from want as a 104 inner join dates as b on a.TimeStamp= b.enddate; NOTE: Table WORK.TEST created, with 25 rows and 6 columns. 105 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds Here in want I have 3477 observations because in this dataset timestamp+1 is extending to all dates until 29dec2028 like for example but i dont want all the dates in oct want only 31OCT2024 which is in dates table . Is there way to do this ? So it occupies less space . contype TimeStamp CP PP MM ERC 30-Sep-24 0 0 0 ERC 1-Oct-24 0 0 0 ERC 2-Oct-24 0 0 0 ERC 3-Oct-24 0 0 0 ERC 4-Oct-24 0 0 0 ERC 5-Oct-24 0 0 0 ERC 6-Oct-24 0 0 0 ERC 7-Oct-24 0 0 0 ERC 8-Oct-24 0 0 0 ERC 9-Oct-24 0 0 0 ERC 10-Oct-24 0 0 0 ERC 11-Oct-24 0 0 0 ERC 12-Oct-24 0 0 0 ERC 13-Oct-24 0 0 0 ERC 14-Oct-24 0 0 0 ERC 15-Oct-24 0 0 0 ERC 16-Oct-24 0 0 0 ERC 17-Oct-24 0 0 0 ERC 18-Oct-24 0 0 0 ERC 19-Oct-24 0 0 0 ERC 20-Oct-24 0 0 0 ERC 21-Oct-24 0 0 0 ERC 22-Oct-24 0 0 0 ERC 23-Oct-24 0 0 0
... View more