vformatwx might do what I need ... View more

: No, I meant what I suggested, creating a copy of original (labeled desired) and using the modify statement using desired as the master. I never like to use the actual master when using modify as there is a chance that the file can become corrupted if the process gets interrupted. The result of my suggested code matches all of your desired results with the exception of Feb 28, 2015 for cusip 3136G0XX. I don't know if that was an error on your part or if there is something you want to do that I didn't see specified. ... View more

Hi, Yes, you need to supply more information.  What system are you generating on, what is your target system, what is the requirement at the customer end. The tagset output is XML as you mentioned, this is a bloated markup language, not really much you can do about this unless you post process the output file and manually trim out some of the non-essentials, but even then you might lose formatting. Some suggestions: Look into compressing your files using other software - ZIP, 7Zip etc.  These can be command line automated from your SAS program and provide very good compression at differing levels. If formating is not required then drop down to CSV which is about as minimal as you can get for text data, this will also open in Excel fine. Learn Open Office Format - your XLSX files are actually ZIP files which contains directories and XML files.  With knowledge of these you can create smaller native Open Office documents. This is not really a suggestion as I don't recommend this approach any more - DDE - create actual binary files which are the old XLS standard.  These are very small, but proprietary so its hard to import/use them outside MS applications and you need SAS/Access to write them which would only be on Windows. Consider what you are doing, is it the best method?  Perhaps separate data from stylesheet (check out Cascading Style Sheets).  Do you need to send formatting information. Possibly - though not looked at myself - use other tagset such as Latex. Oh, and yes, if you have Excel itself, you can write VBA which will open files where specified and loop through saving as .xls which automates the program.  I have posted code on here before for this type of thing. ... View more

In my earlier reply, I used the following 4 do-loops to search for matching records on the lower direction of each of the 4 variables. For example, matrixtable variable, is decremented by unity. It is not efficient for run-time.    do matrixtable = t[m_table] to t[1] by -1 while(rc = 0);       do Lo_date = Lo_date to 7305 by -1 while(rc = 0);          do Lo_freq = f[m_freq] to f[1] by -1 while(rc = 0);             do Lo_amount = a[m_amount] to a[1] by -1 while(rc = 0);                rc = h.find();                if rc = 0 then do; rc = 1; output; end;             end;          end;       end;    end; A better way is to use:           do m_table = m_table to 1 by -1 while(rc = 0);     matrixtable = t[m_table]; and similarly for Lo_freq and Lo_amount. The Lo_date variable takes continous values and so needs no change. For the convenience of reading, I produce the revised program as: The two input data sets used in the program are same as in my previous reply. /** ================================================================================ **/ /**  Loop Modification for efficiency                                                **/ /**================================================================================= **/ data need; array t[3] _temporary_ (81, 85, 100); array f[4] _temporary_ (1,12,40,60); array a[6] _temporary_ (1, 1000, 5000, 10000, 100000, 1000000); format matrixtable table lo_date effectivedate lo_freq freq lo_amount amount intrate;    if _n_ = 1 then do;       retain max_date 19971 ;       if 0 then set matrix;       declare hash h(dataset:'matrix', ordered:'Y', hashexp:16);       h.definekey('matrixtable','Lo_date','Lo_freq','Lo_amount');       h.definedata('intrate');       h.definedone();    end;    set historytrans;    do i = dim(t) to 1 by -1;       if t <= table then do; m_table = i; leave; end;    end;    Lo_date = ifN(effectivedate > max_date, max_date, effectivedate);    do i = dim(f) to 1 by -1 ;       if f <= freq then do; m_freq = i; leave; end;    end;    do i = dim(a) to 1 by -1;       if a <= amount then do; m_amount = i; leave; end;    end;    rc = 0;    do m_table = m_table to 1 by -1 while(rc = 0);       matrixtable = t[m_table];       do Lo_date = Lo_date to 7305 by -1 while(rc = 0);          do m_freq = m_freq to 1 by -1 while(rc = 0);             Lo_freq = f[m_freq];             do m_amount = m_amount to 1 by -1 while(rc = 0);                Lo_amount = a[m_amount];                rc = h.find();                if rc = 0 then do; rc = 1; output; end;             end;          end;       end;    end; keep matrixtable table lo_date effectivedate lo_freq freq lo_amount amount intrate; run; This may not take much time to experiment with the competing program and decide. I will be too happy if OP benefits(in run-time). ... View more

Hi Scott, While I echo the concerns of Sata@SAS and PGStats, I would like to know why you want only Philip in your query. It seems that Janet also qualifies as she is (one of) the oldest while being the heaviest. Are you looking for there to be a unique oldest (i.e. Janet does not qualify because Mary is also 15 and therefore there really isn't a single oldest)? My understanding of your requirement of  "which student is both the heaviest and the oldest for each sex" would include both Janet and Philip. Maybe some context to your real query might help us understand your requirement. Best, Joe ... View more

yes , numx10.2 works thanks... ... View more

So why don't you just make an ID variable? data want ; set have; id+1; run; ... View more

This was most helpful!  I took me a minute to understand, this is the closest I've gotten, seriously thank you!  There is an issue with it bringing over every row though.  I'm not sure what the problem is, it may be with the Table join.  I've tried working on this thing for hours and I can't get it.  ... View more

Thanks PGStat. This is exactly what I want right now. However the 'Messy' text file that I have to handle right now has lot more issues. Will get back to you if my research fail to give a desired outcome. Thanks again for the help!!! ... View more

So if the data is in fixed columns then that is also simple with SAS. You could tell SAS to read from specific columns (input ID $ 1-10) or tell it to use a specific format (input ID $10.). I would use formatted mode so that I did not need to type all of the column numbers. If you input your non-id variables as numeric then it will treat '1' as the number one , '0' as the number zero, and ' ' as a missing value. So if you have a 10 character ID value and then 20 one character variables then you could write: data want ;   infile 'myfile.txt' truncover ;   input id $10. (var1-var20) (1.) ; run; ... View more

Ah, there. Now it works yes. I assumed it was a retain and was messing around with it but I put it in the wrong place in your code. Thanks for the help! ... View more

string=prxchange('s/.*(\d{5})/$1/',-1,strip(number)); ... View more