Since we don't know how many words make up the name, it's easier to use with as the marker.   data HAVE; input WORDS $80.; cards; Assignment was sent to John Smith with the reason and status as Pending Assignment was sent to Peter Smith with the reason and status as Complete. run; data WANT; set HAVE; WORDS2 = substr(WORDS, 1, index(WORDS, ' with')); run;   ... View more

The FINDW function returns the position where a word is first found, which you can then use in SUBSTR. ... View more

So your question is not about how to run the macro.  It its about how to discover the names of the files. That question has been answered many times on this list.   The easiest is to just ask your operating system to tell you names of the files. For example if your SAS program is running on Unix just use the ls command. data files ; infile "ls /mydirectoryname/test*.xlsx" pipe truncover; input filename $256. ; run; Now you have the data you need to run the data step I showed you before to call the macro multiple times.   If your system admins have disabled the ability for you to run operating system commands then you will need to work harder. So use something like this macro to get the list of files: https://github.com/sasutils/macros/blob/master/dirtree.sas ... View more

The article "An easy way to make a "Top 10" table and bar chart in SAS" shows how.    @Ksharp is mistaken about needing the latest version of SAS. This option is available in any version of SAS 9.4, and SAS 9.4M0 shipped way back in in 2013.   If you aren't using SAS 9.4, there is a link in the blog post to an older technique. ... View more

@pappusrini wrote: Thanks. Does it matter if the time variable is the “time part” of date/time variable? I have the time variable as: 8Am 9Am But this is the time part of date/time variable. Thanks Try it.  ... View more