I see. Since we have a date for each cash flow, the IRR function should take care of timing issues and give us an annulized rate, regardless of how many years are involved. ... View more

This works amazingly well, despite that I am not 100% sure why it works. ... View more

@xyxu wrote: If I use the text editor to inspect, it's really hard to distinguish dates with different formats, e.g., "1/3/2019" could be either "Jan 03, 2019" or "Mar 01, 2019". When I open the file in Excel, the date is either left-aligned or right-aligned, allowing me to guess which is the month and which is the day (by comparing with dates like "22/3/2019" that has the same alignment).    I am feeling insecure about getting this into SAS. How do we know we are getting the date right? Looking at the file, it was obvious to me that the dates were in DMY order; the second group of numbers never exceeds 12. Using the data step I proposed, any date not in DMY order would cause a log message, so you have an automated check.   PS all this would be moot if people had the intelligence to use the ISO 8601 standardized YYYY-MM-DD date format when moving data. ... View more

This approach works amazingly well. For the header issue, I can easily delete observations that are written from headers. Thank you! ... View more

Some notes on creating an INFORMAT to handle NA, N/A, null and other text to replace missing values. You could have the N/A text converted to a special missing if you need to distinguish it from the normal missing or empty value. To have the informat just use the normal input method you can use the keyword _SAME_ on the right of the equal sign.   To specify the nested use of another informat use square brackets, [ ], around the informat name on the right of the equal sign.  There is no need to use the BEST alias for the normal numeric informat, what the documentation calls the w.d informat.  If you want to use an alias it might be clearer to use F instead BEST, it is not clear what "best" would mean in terms of an informat anyway. proc format ; invalue na (upcase) 'N/A','NA' = .N other = _same_ ; run; data test; input x na32. ; put x= ' ' _infile_; cards; 12 0.5 n/A NA . 12345 ; proc format ; invalue na_comma (upcase) 'N/A','NA' = .N other = [comma.] ; run; data test; input x na_comma32. ; put x= ' ' _infile_; cards; 12 0.5 n/A NA . 12,345 ; ... View more

Note that the LOWCASE() function calls has nothing to do with this issue.   SAS stores character variables as fixed length.   If you assign a value that is shorter than the max then it is padded with spaces. If you assign a value that is longer it is truncated. ... View more

If you want a more arcane solution, try: data want; set have; by group; length new_var 8; retain new_var; new_var = ifn(first.group, 1, new_var) + sum(0, flag); run; ... View more