A common requirement in programming is to be able to look ahead at the next record or back at the previous record in order to make a calculation or comparison. In the SAS DATA step, look-ahead is trickier than look-back. In SQL, there is symmetry. For example, start with   data have; input ID $ Index Measure; cards; A 1 11 A 2 12 A 3 13 B 1 21 B 2 22 ; How can each observation be extended to include the next and previous values of MEASURE, with missing values in the appropriate places at the beginning, end, and ID boundaries? In other words, how can one derive this data set:     Next_ Prev_ ID Index Measure Measure Measure A 1 11 12 . A 2 12 13 11 A 3 13 . 12 B 1 21 22 . B 2 22 . 21 Here's a DATA step solution:   data look_both_ways; set have; by ID; set have ( firstobs = 2 keep = Measure rename = (Measure = Next_Measure) ) have ( obs = 1 drop = _all_ ); Prev_Measure = ifn( first.ID, (.), lag(Measure) ); Next_Measure = ifn( last.ID, (.), Next_Measure ); run; Notice that the look-back values are supplied by the LAG function, while the look-ahead requires an additional SET statement coordinated carefully with the first one. Here's a PROC SQL solution in the form of a chain of LEFT JOINs: proc sql; create table look_both_ways as select have.* , next.Measure as Next_Measure , prev.Measure as Prev_Measure from have left join have as prev on have.ID = prev.ID and have.Index = prev.Index + 1 left join have as next on have.ID = next.ID and have.Index = next.Index - 1 ; quit; Notice that this SQL solution depends on the INDEX values with their equal increments, not on the order of the rows in the given table. Here's another SQL solution, one which does not need the INDEX column but rather depends on the strict monotonicity of the column (MEASURE in this case) for which we need the look-ahead and look-back. Instead of a chain of joins, there is a structure of nested queries. create table look_both_ways as select lookback.*, next.Measure as Next_Measure from ( select have.*, prev.Measure as Prev_Measure from have left join have as prev on have.ID = prev.ID and prev.Measure < have.Measure group by have.ID, have.Measure having prev.Measure = max(prev.Measure) ) as lookback left join have as next on lookback.ID = next.ID and next.Measure > lookback.Measure group by lookback.ID, lookback.Measure having next.Measure = min(next.Measure) ;  This may be a bit easier to follow if broken into two statements, with the inline view replaced by a named view. create view lookback as select have.*, prev.Measure as Prev_Measure from have left join have as prev on have.ID = prev.ID and prev.Measure < have.Measure group by have.ID, have.Measure having prev.Measure = max(prev.Measure)  ; create table look_both_ways as select lookback.*, next.Measure as Next_Measure from lookback left join have as next on lookback.ID = next.ID and next.Measure > lookback.Measure group by lookback.ID, lookback.Measure having next.Measure = min(next.Measure)  ;  All of the solutions produce the specified results, but that's only because of the way the example is contrived. The DATA step "reads" the order of the observations in the source. The first SQL view reads the evenly separated INDEX values. The second SQL solution reads the sequence of the MEASURE values. In general the three methods will produce different results.   Originally posted by Howard Schreier on sasCommunity.org. ... View more

The MONOTONIC function is an undocumented SAS function. It needs no input parameters and if any input parameters are provided, these are ignored. The function counts the number of times it is called and returns an ascending sequence of integers, starting at 1. The function can be used in an expression and is syntactically valid in both a DATA Step and an SQL procedure. However, it may produce unexpected results in some circumstances.   Syntax variable = MONOTONIC() ;   Usage The MONOTONIC function is often presented as a means of adding sequence numbers to table rows. Several papers present it as a solution to this programming problem. However, the function is undocumented and has some limitations, including: If the MONOTONIC function is used in an SQL procedure that aggregates data then the function may return non-sequential or missing results. If two different statements use the MONOTONIC function then a separate number sequence is returned for each statement. These limitations constrain where and when the MONOTONIC function can be used appropriately.     In a DATA step, one can use the automatic variable _N_, which counts the number of iterations of the DATA step. Or one can use a RETAIN statement to declare a counting variable that is incremented programmatically under given conditions.   When using the OPEN function in a DATA step with FETCHOBS, the CUROBS function can return the current observation number.   Further reading SAS Papers Paper 040-29: Helpful Undocumented Features in SAS® Wei Cheng, ISIS Pharmaceuticals, Inc., Carlsbad, CA Paper 257-2013: Top 10 Most Powerful Functions for PROC SQL by Chao Huang and Yu Fu, Oklahoma State University Adding Serial Numbers to SQL Data by Howard Schreier Usage Note 39458: Adding row numbers to list tables in SAS® Web Report Studio  This article was originally published by Cameron on sasCommunity.org. ... View more

The SETINIT Procedure is an undocumented procedure that identifies which SAS components or products are installed and what the license expiration date is for the system and each component. It also gives the site name and number as well as the system birthday, operating system and any grace or warning period for license expiration.   Syntax PROC SETINIT <options> ; RUN ; The available <options>, which can be omitted, include: NOALIAS Which removes the alias names for a product bundle that is licensed as a package, for example an ACADEMIC bundle, and lists each licensed product individually. RELEASE = The RELEASE = option, is an option expected by the parser, which recognizes RELEASE = " string " as being valid syntax. The contents of the quoted string is not documented, although an empty string as well as a blank space are accepted as being valid.   Usage This procedure can be run before or after the SAS system license expires or is renewed to obtain basic information about the SAS installation and components. This information may be needed by SAS Support. If you want to identify which version of each SAS component is installed, then use the  PRODUCT_STATUS procedure.   This article was originally published by Cameron on sasCommunity.org. ... View more

  %let WeekDate = %sysfunc(date(),weekdate29.) ; %let DateCCYY = %sysfunc(date(),year4.) ; %let DateMonth = %sysfunc(date(),MonName9.) ; %*September:9; %let Date_MM = %sysfunc(date(),Month2.) ; %*01:12; %let Date_DD = %sysfunc(date(),Day2.) ; %*01:31; %let Date_Day = %sysfunc(date(),downame9.) ; %*Wednesday:9; %let DateJul = %sysfunc(date(),julian7.) ; %*ccYYddd; %let DateJulDay= %sysfunc(date(),julday3.) ; %*ddd; %let Date_Qtr = %sysfunc(date(),qtr1.) ; %*1:4; %let Time = %sysfunc(time(),time8.0) ; %*HH:MM:SS; %let Time_HH = %scan(&Time.,1,:) ; %let Time_MM = %scan(&Time.,2,:) ; %let Time_SS = %scan(&Time.,3,:) ; %let TimeAMPM = %sysfunc(time(),timeAmPm8.0) ; %*HH:MM AM; %put Weekdate = &WeekDate ; %put DateCCYY = &Dateccyy ; %put DateMonth = &DateMonth ; %put Date_MM = &Date_mm ; %put Date_DD = &Date_dd ; %put Date_Day = &Date_Day ; %put DateJul = &DateJul ; %put DateJulDay= &DateJulDay; %put Date_Qtr = &Date_Qtr ; %put Time = &Time ; %put Time_HH = &Time_hh ; %put Time_MM = &Time_mm ; %put Time_SS = &Time_ss ; %put TimeAMPM = &TimeAmpm ;   Log:   24 %put Weekdate = &weekdate ; Weekdate = Wednesday, November 20, 2002 25 %put DATECCYY = &dateccyy ; DATECCYY = 2002 26 %put DATEMONTH = &datemonth ; DATEMONTH = November 27 %put DATE_MM = &date_mm ; DATE_MM = 11 28 %put DATE_DD = &date_dd ; DATE_DD = 20 29 %put DATE_DAY = &date_day ; DATE_DAY = Wednesday 30 %put DATEJUL = &datejul ; DATEJUL = 2002324 31 %put DATEJULDAY= &datejulday; DATEJULDAY= 324 32 %put DATE_QTR = &date_qtr ; DATE_QTR = 4 33 %put TIME = &time ; TIME = 15:24:01 34 %put TIME_HH = &time_hh ; TIME_HH = 15 35 %put TIME_MM = &time_mm ; TIME_MM = 24 36 %put TIME_SS = &time_ss ; TIME_SS = 01 37 %put TIMEAMPM = &timeampm ; TIMEAMPM = 3:24 PM       see also:     %Let StDate = &SysDate.; %*Let StDate = %sysfunc(date(),yymmddn8.); %Put StDate: &StDate.: %let DatePart = %sysfunc(datepart(%substr(&SysProcessId.,1,16)x)); %let Year = %sysfunc(year(&Datepart.)); %let Month = %sysfunc(putn(&Datepart., month2.)); %let Month = %sysfunc(putn(&Month. , z2.)); %let MonthName = %sysfunc(putn(&Datepart.,monname3.)); %let Day = %sysfunc(putn(&Datepart., day.)); %let Day = %sysfunc(putn(&Day. , z2.)); %Put MyFormatedDate: &Year._&Month.&MonthName.&Day.   Thanks to Ronald Fehd for publishing this on sasCommunities.org!   ... View more

Proc Delete is faster than either PROC DATASETS or PROC SQL.  Here are a few examples of how SAS programmers delete SAS tables.     proc delete data = libref.dataname; run; proc sql; drop table libref.dataname; quit; proc datasets library = libref; delete dataname; run; data _null_; length dd $8; rc = filename(dd,cats(pathname('libref'),'\dataname.sas7bdat')); rc = fdelete(dd); put _all_; run;      Since SAS 9.4, PROC DELETE has had more capabilities than ever -- find out more in Chris Hemedinger's blog.   This was originally posted by Ronald Fehd on sasCommunity.org. ... View more

SAS data sets consist of a descriptor portion and a data portion that contains the data values. The descriptor portion of a SAS data set contains detailed information about the data set. This information includes: The name of the data set and its member type The date and time the data set was created The number of observations The number of variables The engine type Reading the descriptor portion is one of the most efficient and quickest ways of determining the number of observations in a SAS data set.  Here are some examples:   /* SAS V9.x and higher */ data _NULL_; if 0 then set sashelp.class nobs=n; call symputx('nrows',n); stop; run; %put nobs=&nrows; The "if 0" is a a conditional statement designed to never be executed at run-time and works since the "set sashelp.class" gives the header information of the data set class to the compiler which sets up the PDV accordingly, but is skipped in the execution time because the if condition is false. nobs is a SAS automatic variable which contains the number of records in the data set named in the set statement. The code, nobs = n, places the value of nobs (the number of records in the sashelp.class dataset) in the variable n. The STOP statement is used to prevent an endless loop because EOF is never reached. Checking for empty data set Many times users don't need to know how many obs there are, just whether there are any. In such cases, a data step like the following might be just what is needed. This technique also has the advantage that it works for views and other data sets where the number of observations is not available in the data set header (Don Henderson's tip from sascommunity.org).   data _null_; call symput('AnyObs','0'); /* set to 0 for No */ set &data(obs=1); call symput('AnyObs','1'); /* if got here there are obs so set to 1 for Yes */ run;   Access the descriptor information in SAS macro The metadata can also be accessed by the macro language using DATA step functions. The ATTRN function is used with the NLOBS argument in the %OBSCNT macro shown below to retrieve the number of non-deleted observations from the meta data.   %macro obscnt(dsn); %local nobs dsnid; %let nobs=.; %* Open the data set of interest; %let dsnid = %sysfunc(open(&dsn)); %* If the open was successful get the; %* number of observations and CLOSE &dsn; %if &dsnid %then %do; %let nobs=%sysfunc(attrn(&dsnid,nlobs)); %let rc =%sysfunc(close(&dsnid)); %end; %else %do; %put Unable to open &dsn - %sysfunc(sysmsg()); %end; %* Return the number of observations; &nobs %mend obscnt;   Using DICTIONARY tables and PROC SQL SAS dictionary tables can also be used to load the number of observations into a macro variable:   proc sql noprint; select nobs into :nobs separated by ' ' from dictionary.tables where libname='SASHELP' and memname='CLASS'; quit;   %put TNote: nobs=&nobs;   Thanks to Alberto Negron and Tom Robinson for posting this help on sascommunity.org! ... View more

One of the best ways to understand the INTNX and INTCK functions and how they work is to see some easy examples. Thus, in this article you will find some. (Note: this article originally appeared on sasCommunity.org, written by Victor Popovich.)    If you prefer to learn by watching (while listening to some funky original music), check out this tutorial about SAS Intervals from @derekmor54 (Mr. Dates and Times):     For more information on the INTCK and INTNX functions, see INTCK and INTNX: Two essential functions for computing intervals between dates in SAS, an article by @Rick_SAS.   Difference between INTNX and INTCK functions These two functions complement each other: INTCK computes the difference between two dates, while INTNX enables you to add time units to a date value.   INTCK function  Computes the number of time units between two date (or datetime) values. For the time unit, you can choose years, months, weeks, days, and more. INTNX function Computes a SAS date (or datetime) that is a specified number of time units away from a specified date (or datetime) value.   Trick to remember: INTCK - INTerval ChecK  INTNX - INTerval NeXt    INTNX Function Examples According to SAS documentation this function Increments a date, time, or datetime value by a given interval or intervals, and returns a date, time, or datetime value.  Example 1. Some simple examples of using INTNX function:   format day week month_ year date9.; day=intnx('day', '01FEB2010'd, 7); /* +7 days */ week=intnx('week', '01FEB2010'd, 1); /* 01 of Feb 2010 is Monday*/ month_=intnx('month', '01FEB2010'd, 2); /* +2 month */ year=intnx('year', '01FEB2010'd, 1); /* +1 year */ The result values will be: day = 08FEB2010 (+7 days) week = 07FEB2010 (next Sunday) month_ = 01APR2010 (next 1st day of 2nd next month) year = 01JAN2011 (next 1st day of next year)   Example 2. Multiplying and shifting intervals. You may change the first argument (interval) by adding digits as in the example below.   format day week year date9.; day=intnx('day2', '01FEB2010'd, 2); /* two of 2-days intervals */ week=intnx('week1.3', '01FEB2010'd, 1); /* 01 of Feb 2010 is Sunday. 3rd day of the week is Tuesday */ year=intnx('year1.3', '01FEB2010'd, 1); /* next year | third month | 1st day*/ The result values will be: day = 05FEB2010 (+2 days twice) week = 02FEB2010 (Next Tuesday) year = 01MAR2011 (Next Year's third month (March) 1st day) You are free to combine intervals by multiplying and shifting it. However, please be careful when contructing complex interval specifications.   Example 3. Datetime and time formats. The INTNX function may cope with datetime, time and date formats. The above examples are based on date format. The example below uses datetime and time formats.   format seconds minutes hours days weeks datetime20.; format t_seconds t_minutes t_hours time9.; seconds=intnx('second', '01FEB2010:00:00:00'dt, 1); minutes=intnx('minute', '01FEB2010:00:00:00'dt, 1); hours=intnx('hour', '01FEB2010:00:00:00'dt, 1); days=intnx('dtDay', '01FEB2010:00:00:00'dt, 1); weeks=intnx('dtWeek', '01FEB2010:00:00:00'dt, 1); t_seconds=intnx('second', '00:00:00't, 1); t_minutes=intnx('minute', '00:00:00't, 1); t_hours=intnx('hour', '00:00:00't, 1); The result values will be: seconds= 01FEB2010:00:00:01 (next second) minutes= 01FEB2010:00:01:00 (next minute) hours= 01FEB2010:01:00:00 (next hour) days= 02FEB2010:00:00:00 (next day) weeks= 07FEB2010:00:00:00 (next Sunday) t_seconds = 00:00:01 (next second) t_minutes = 00:01:00 (next minute) t_hours = 01:00:00 (next hour) and so on.   Example 4. Alignment within the interval.   format beginning middle end sameday date9.;   beginning=intnx('month', '05FEB2010'd, 1, 'b'); middle=intnx('month', '05FEB2010'd, 1, 'm'); end=intnx('month', '05FEB2010'd, 1, 'e'); sameday=intnx('month', '05FEB2010'd, 1, 's'); The result values will be: beginning = 01MAR2010 (1st day of the next month) middle= 16MAR2010 (middle day (16 of 31) of the next month) end= 31MAR2010 (last day (31) of the next month) sameday = 05MAR2010 (the same day (5) of the next month) You might find it helpful to use the INTCK function along with the INTNX.   INTCK Function Examples Everyone knows that the INTCK function returns the integer count of the number of interval boundaries between two dates, two times, or two datetime values. In the following Example1 you see how this function works within some basic intervals: Example 1. Date arguments.   years=intck('year','01jan2009'd,'01jan2010'd); SEMIYEAR=intck('SEMIYEAR','01jan2009'd,'01jan2010'd); quarters=intck('qtr','01jan2009'd,'01jan2010'd); months=intck('month','01jan2009'd,'01jan2010'd); weeks=intck('week','01jan2009'd,'01jan2010'd); days=intck('day','01jan2009'd,'01jan2010'd); In the example above we count the difference between two dates: 01-Jan-2009 and 01-Jan-2010 (one year). The result values will be: years = 1 semiyears = 2 quarters= 4 months = 12 weeks = 52 days = 365 Example 2. Datetime arguments.   hours=intck('hour','01jan2009:00:00:00'dt,'01jan2010:00:00:00'dt); minutes=intck('minute','01jan2009:00:00:00'dt,'01jan2010:00:00:00'dt); seconds=intck('second','01jan2009:00:00:00'dt,'01jan2010:00:00:00'dt); The result values will be: hours= 8760 minutes= 525600 seconds= 31536000 Example 3. Time arguments.   hours=intck('hour','00:00:00't,'12:00:00't); minutes=intck('minute','00:00:00't,'12:00:00't); seconds=intck('second','00:00:00't,'12:00:00't); In the example3 (above) we count the difference between two time values: 00:00:00 (midnight) and 12:00:00 (midday). The result values will be: hours= 12 minutes= 720 seconds= 43200 Important notice! The INTCK functions does not count the number of complete intervals between two values. The example below shows how it calculates the 'YEAR' difference between 31-Dec and 1-Jan:   Example 4. Boundaries.   years=intck('year','31dec2009'd,'01jan2010'd); The result values will be: years= 1 Since you know that it is just ONE DAY and it is not a YEAR the results could be mistakenly considered as incorrect. Please be aware that it returns the integer count of the number of interval boundaries between two dates. In the example above the boundary is the value of '01JAN2010'd that is included (just once) into the interval.   To solve for this use the 'CONTINUOUS' method (shortened to 'C'): wholeyears=intck('year','31dec2009'd,'01jan2010'd,'C'); You can use a combination of the 'CONTINUOUS' option of the INTCK function and the 'SAME' option of the INTNX function to count time the way we count chronological age. data a(keep=start end wholeyears wholemonths wholedays); start = '31dec2009'd; end = '01jan2010'd; wholeyears=intck('year','31dec2009'd,'01jan2010'd,'C'); start2 = INTNX('year',start,wholeyears,'S'); wholemonths=INTCK('month',start2,end,'C'); start3 = INTNX('month',start2,wholemonths,'S'); wholedays=INTCK('day',start3,end,'C'); format start end DATE.; run; proc print;run; The result will be: Obs start   end     wholeyears wholemonths wholedays   1 31DEC09 01JAN10          0           0         1   Some operations with changing formats are also helpful when you deal with the INTCK function. Please take a look at the next examples. I believe it is quite easy to understand how it works.    Example 5. Datepart() Timepart() functions.   format a1 b1 date9.; a0='01jan2009:00:00:00'dt; b0='01jan2010:00:00:00'dt; a1=datepart(a0); b1=datepart(b0); days=intck('day',a1,b1); The result value will be: days = 365 format a1 b1 date9.; a0='01jan2009:00:00:00'dt; b0='01jan2010:12:00:00'dt; a1=timepart(a0); b1=timepart(b0); hour=intck('hour',a1,b1); The result value will be: hour= 12   Thanks to Victor Popovich for providing this help on sascommunity.org! ... View more