As mentioned above, here's a thread with some stress testing with examples using hash tables to achieve your desired result https://communities.sas.com/thread/46818 Again though, with marginally small dictionary of good words, you won't see any difference with your current _temporary_ array approach. ... View more

Scott, I SUGI 26 was in 2000 or 2001? That was before SAS developpers implemented hash objects. This paper essentially describes the hard coded version of hash hash objects back when you couldn't use the object already. The underlying concept of the hash object is that it builds a unique fast searchable key tied to a pointer on the data for that unique key. The object does the binary hashing discussed in the paper. At this stage in computer technology evolution though, looping over 150 array cells versus performing a hash lookup on  a hash object with 150 cells is marginally better. He would need to tens of millions of keyword3 to notice any difference. Nonetheless, a hash table approach to a similar problem has been posted elsewhere on these forums recently ... View more

Reeza, _TEMPORARY_ array do not reference particular columns. They're a mean to store values in memory with no special indexing (like that of hash tables) to search them. Arrays that are not temporary and are specified with variable ranges or name list are not temporary and act basically as an addition to the data vector. Oddly enough array _temporary_ don't get their values reinitiated to missing at each data loop. They remain in memory as hash objects until the end of the data step. That is, they are not really temporary to the iterations of the data step (but they are temporary to the output dataset as they are not automatically output since they are not tied to any data vector variables). The above syntax for array _temporary_ is definitely right. I use temporary arrays as small dictionaries all the time. As to the OP, there was a thread about 2 months back that depicted a different approach. That of reading your keyword3, word by word and then doing a match lookup to a hash table acting as a dictionary (likely more efficient than kindex or find functions). I'll try to find the thread back in my activity and link it if you want. But the TRIM() I mentionned above should make your current code work as intended ... View more

A book I have found useful is The essential guide to SAS Dates and Times Art Carpenter's SAS software series by Derek P. Morgan It does not, however, cover many other numeric formats like hex, binary, etc. as you can see by the books' name. The date format you are looking for to output full month day, year could be built with a picture statement in proc format or with the existing WORDDATEw. format. It's backdraw is that it adds leading blanks to shorter month names. WORDDATE18. is the minimum  width to get full month name parsed, with necessary leading blanks for all but september. Anything below 18 will cut to 3letter abbreviation. Anything beyond 18 will add however much more leading blanks. I'm not sure of any detailed way to gather information on all format from within SAS (like one you could query and do manipulation to document another dataset with). There's always sashelp.vformat but there are no description of the formats so it is not the most useful dictionary table. ... View more

Hopefully the images are viewable. The one thing with leaving them all open and using documents or windows commands to close is that you would have needed to save them all before. With that said, if you did, you wouldn't really need the documents and windows command as you could simply use close.all command. If you want that small APP for DDE, hit http://support.microsoft.com/kb/128185/en-us ... View more

I'm with Reeza, I'd like to see more of the code to figure out ways around. As an alternative to test, you could strip the name of your workbook from the file path and use activate command to get the targetted file.xls as the current active workbook and then close it. Like %let wbname='file.xls'; put "ACTIVATE("&wbname.")"; This is all untested, I'm just reading through the no longer supported DDE documentation from microsoft. ... View more

Indeed the macro variable would not resolve. My bad. You can just swap quotes and double quotes and it would resolve properly ... View more

Hi Js, Based on SAS documentation, it appears as though if you use paths in your dde triplet, they need to be quoted within the quote so %let filenm = "c:\temp\file.xls"; filename DDEcmds dde 'Excel|&filenm.'; should resolve your current error. Otherwise, you could consider using the blank workbook approach and use commands to open/modify/save/close the workbook within your data null statement filename DDEcmds dde 'excel|system'; ... View more

That's actually fairly tricky to add within a single data step since you can't get to enforce a zn.w format. I see 2 alternatives 1: Use PRX to capture number of leading 0s from your lower boundary and then control the number of those that are output based on your _i counter (like remove one 0 from the captured lead for each increment in log10(_i)). It would resemble something like this (untested) length lead $20.; /*just so that prxchange output does not get converted to numeric*/ ...      do _i=prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v1)  to prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v2);          IF(prxmatch("/\D+/",strip(_v1))) THEN             lead=prxchange('s/(\w*\D+)(0*)(\d+)/$2/',-1,_v1); /*you could change \d+ to [1-9]\d+ or something to clearly identify the purpose of 0* but since * is greedy its only a minor efficiency issue */            new_var=cats(prxchange('s/(\w*\D+)(\d+)/$1/',-1,_v1),  length), substrn(left(lead), 1, length(trim(left(lead)))-(floor(log10(_i))-floor(log10(prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v1)))) )            ,_i); ... drop lead; 2: Another way would build a series of case based on the length of the fully captured digit as a string for the lower bound and use put& zn.w formats e.g.      do _i=prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v1)  to prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v2);          IF(prxmatch("/\D+/",strip(_v1))) THEN                zlen = length(prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v1));           if zlen=1 then new_var=cats(prxchange('s/(\w*\D+)(\d+)/$1/',-1,_v1),_i);           else if zlen=2 then new_var=cats(prxchange('s/(\w*\D+)(\d+)/$1/',-1,_v1),put(_i, z2.);           ...           else if zlen=8 then new_var=cats(prxchange('s/(\w*\D+)(\d+)/$1/',-1,_v1),put(_i, z8.)); ... View more