Sorry, it was the xls option. I'll try to try the hotfix this afternoon. ... View more

No I need to figure out if something similar can be done other ways. The person in my office needing this is using ODS HTMLCSS to output formatting. PROC EXPORT is just the dataset from what I can tell. ... View more

Thank you so much! That works for me to, and appears to update correctly (running a 3rd call separately from the first two). ... View more

I had to download and install the SAS PC Files Server. Changed the DBMS to EXCELCS. No errors in the log. File won't open. (Excel cannot open the file 'temp.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.) ETA: the file is twice the size (10 KB instead of  5 KB), so I guess it is possible that it is writing both tabs - but I still can't open the file. ... View more

If I change dbms=xlsx to dbms=excelcs, I get log error: failed to connect to server: . When I researched this, it was mentioned that I could sub xlsx instead to solve this problem. I don't know if I am causing other issues by doing so. Ideally I would like to be able to write to the same file (in a new tab) each subsequent year. But I haven't found anything that leads me to that yet. Thank you for your help! ... View more

@Reeza - thank you. I have no experience with tagsets. I have successfully written to 32bit Excel using the code below. This is what I was trying - which created an xlsx file that I could open, but with only one sheet (the last one). %macro test (var=,sheet=); proc export    data=sashelp.class (where=(sex="&var"))                     outfile="C:\temp.xlsx"                     dbms=xlsx                     replace;                     sheet="&sheet"; run; %mend test; %test(var=F,sheet=Female); %test(var=M,sheet=Male); ... View more

Thank you so much. So, I used your code above (but changed the file= to a different location) and it writes the file (I see it appear in the directory) - but it will not open. No error in the SAS log, but when opening the .xls file, I get this: "The file you are trying to open, 'sample.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" [Yes] [No] [Help] Selecting [Yes] yields Unable to read file. I am using Windows 7 64bit and SAS 9.3 64bit. Office is 32bit. I know with proc export, I had to make a change for it to work with these settings. I have never used (or heard of) ods tagsets.excelXP until trying to solve this problem. Thank you for your help. ... View more

Tom, Can you please explain what the constant('big') is doing? Thank you for the answer, it appears to do exactly what I want. Greg ... View more

Well, because the obs directly after 100 could be close enough to also be considered. Say for example that obs 100 is 403.25 miles away, obs 101 is 403.26, obs 102 is 404.10 obs 103 is 406.07. We are find dropping obs 103 and higher, but would want to keep 101 and 102. Starting at 0 miles and incrementing by 5 miles until we get a number >= 100. We want at least 100 observations, but want to stop once at least 100 is reached - in increments of 5 miles. ... View more

Tom, I'm not doubting you at all - but perhaps I am misunderstanding you. The code that I posted above works, the %let statement in the middle works for each department length correctly. I will definitely try call symputx to get rid of clutter. Thank you for your time. Now I have to find out if it is possible to append a new excel sheet to an existing excel workbook, but that is a question for another area. Call Symputx worked perfectly - in fact, it's a bit hilarious as originally I had that line (without the x) and it was causing the spaces. I expanded to 5 lines, then 3 lines (based on Reeza's %trim suggestion) and now back to the original 2 lines, except with the x. ... View more

Well, after much playing, I got something similar to what SteveNZ suggested, but even more clunky. Here is the the gist of the program: An academic program review for all departments at my university. It is the same report, for all of the different departments, so we are using macros to program the logic once, and then run for the various departments (based on Major Codes, not department names - this wasn't my call). The report would be output using a file="Academic_Program_Review_for_&Year._&Department..rtf" That last variable would have a varying degree of spaces depending on the department name, for instance, Visual Arts would come with 24 spaces before the .rtf My boss would not accept that, and it is their program (I'm just attempting to assist on certain aspects). So this is what I came up with: IF MAJOR_COHORT IN &major_list; dept1=TRIM(put(major_cohort,$dept_INV.)); /*Because of &dept_inv., Dept1 has a length of $36 regardless of actual length of the current department name, trim does nothing here*/ CALL SYMPUT ("DEPARTMENT1",DEPT1);        /*Create a variable of the department name (this was previously used in the file name above*/ %let test = %length(&department1);        /*Use the above variable to count the length of the current department name, Visual Arts = 11*/ DEPT = put(DEPT1,$&test..);               /*Create a new DEPT with the length of the current Department, Visual Arts would be 11*/ CALL SYMPUT ("DEPARTMENT",DEPT);          /*Create a new variable for use in the .rtf file name (see above) without the trailing spaces/blanks*/ Thank you all for your time helping me. ... View more

So I'm trying to implement this, and I'm running to an issue here: by id ; The Graduates table does have an 'id' field, but the CIP table does not. Both have 'major' and 'lvl_degree', which is how they were being merged originally. Nevermind - I figured out that I needed to do it a different way. If first sort and merge the 2 datasets by major lvl_degree in a previous step. Then, in the do until (last.id), I set (instead of merge) the previously merged dataset by id. This appears to do exactly what I want. Thank you all so much again! ... View more