11-13-2015 06:50 AM - edited 11-13-2015 10:08 AM
Here is what I want to do:
1) calculate some values based on a subsample of my data defined by having the "list" variable of the data equal to the macro variable &list
2) open c:\temp\space dir\template.XLS , sheet "template" and write the values to a few named ranges
3) save the template to a temporary file and sheet, say c:\temp\space dir\temp\list_&list.xls , sheet "list_&list"
4) loop for &list in (4400 4900 4512)
5) combine the three created sheets in a final file named c:\temp\space dir\output.xls
1 is easy, 2 is done with LIBNAME EXCEL.
I have managed to do 5) by calling a VBS script (code below) with the following sas code (notice the single quote followed by a double quote)
command = '"c:\temp\space dir\script_copy_worksheets_to_workbook.vbs"';
I just can't seem to do 3) because it involves passing a macro variable value between single quotes. For example, I wish the macro variables resolves in the following code. I could then (maybe, i'm a noob) figure out a VBS script that would rename the sheet to the same name as the file name.
%let file_from= "c:\temp\space dir\templace.xls";
%let file_to= "c:\temp\space dir\temp\list &list";
command = '"copy &file_from. &file_to."';
I have spent way too much time on this, any help would be greatly appreciated.
'~~> Change Paths as applicable
Dim objExcel, objWorkbook, wbSrc
Dim strFileName, strDirectory, extension, Filename
Dim objFSO, objFolder, objFile
strFileName = Wscript.Arguments.Item(1)
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
extension = "xls"
strDirectory = Wscript.Arguments.Item(0)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strDirectory)
For Each objFile In objFolder.Files
If LCase((objFSO.GetExtensionName(objFile))) = LCase(extension) Then
Filename = objFile.Name
Filename = strDirectory & "\" & Filename
wbSrc.Sheets(1).Name = left(objFSO.GetFileName(objFile), Len(objFSO.GetFileName(objFile))-4)
'~~> Close and Cleanup
Set objWorkbook = Nothing
Set objExcel = Nothing
11-13-2015 07:02 AM
I think I got part of 3) working based on Tim's post here:
file_from= "c:\temp\space dir\template_ne_pas_modifier.xls";
file_to= "c:\temp\space dir\temp\\liste&liste..xls";
command = 'copy "' || file_from || '" "'|| file_to ||'"';
11-13-2015 08:06 AM
Erm, why? Sounds like your drastically over complicating things. Why not just use tagsets.excelxp to write a file out with 3 sheets? If for some reason you have a template Excel file which needs to be updated, exprt your results to CSV from SAS. Then in your template file create a small bit of VBA to open that CSV and update the template based on the values in the CSV.
11-13-2015 08:21 AM
thanks for replying!
It sure does feel complicated, but I cant seem to find a simpler way - I do need to use the template , so no excelxp. I'm not sure how your workflow would be simpler, though.
So your workflow would be as follow:
-Create 15 CSV files from SAS
-Open the template file 15 times, import the CSV each time. and save the template into a new workbook
-again, merge the 15 worksbooks into one.
this having to be done repeatedly a couple times a week.
11-13-2015 09:06 AM
Well, firstly, it sounds like you have a mess of process there. I would try to push back on what needs to be done, maybe the recipient can use what outputs straight from SAS. There are techniques to help, i.e. mention high costs to maintain, pushed back timelines, its amazing how quickly minds change.
If you really can't, then what I suggested wouldn't involve opening the template 15 times. You would export the data, decide on some filenames, why can the data from SAS not be in one file for instance? Its hard to say without seeing what you have. But I will assume that there will be 3 files, called file1.csv, file2.csv, file3.csv. In the template file, I create a small VBA macro program. This will be a loop of 1-3. In the loop I open the fileX.csv where X is the loop incrementor. Once the file is open then I can take that data and paste it where I like in the template file. Then close the CSV, and do the loop again. Once the loop finishes then save the tempalte file somewhere. So instead of trying to push data out from SAS, suck it into Excel.
Of course, your whole problem is the process in the start, this whole business of trying to integrate a tool - Excel - which was never meant for these things, into a pipeline generally speaking decided upon by people not knowing a) what they want really, b) how things work. I always push back, as soon as Excel is mentioned.
11-13-2015 10:08 AM
I see what you mean. The thing is that everything has to be as marketing-proof as possible and if everything can be generated by a batch job in SAS then I'm happy.
I did something similar to what you say, I just skipped the CSV part and implemented the vba loop in VBS script called from SAS.
Here's what I do :
Generate a lot of one-sheet excel files and put them in a folder
a) some (those using templates) are generated using LIBNAME excel and are then copied under their own name using
command = 'copy "' || file_from || '" "'|| file_to ||'"';
b)some (those using sgplots and other graphs) are generating using LIBNAME msoffice2k
when that is done, I call an updated version of my vbs script that gathers all these excel files into one file.
that's it.data _null_;
command = '"path\script_copy_worksheets_to_workbook.vbs" "' || source_folder || '" "' || destination || '"' ;
12-08-2015 01:37 PM
12-09-2015 04:11 AM
Personally I would do all outputs as RTF or PDF. The simple reason is separation. What the eye wants is not what the computer wants. This is where there is a lot of problems. Peolpe think that Excel files are simple to move into a structured environment, they are not, for many reasons. However people do seem to understand that Word files and PDF's can't be imported, strange but that is what i have found.
Proc report can output to any number of destinations, and that is defined by:
- the structure of your data
- the template used
- to some extent the destination
- the code you use in the proc report
12-09-2015 05:30 AM - edited 12-09-2015 05:42 AM
Proc Report and ODS PDF do seem to be the way to go. It's just that Proc report's output is so ugly and creating a nice template in Excel, filling it and converting it to PDF is just so easy.... Eye-candy is important since this report is sent to 1500 persons.
Here is a mock-up of what I am trying to achieve. There are 50 teams and large teams could span up to 3 or 4 pages.
I'll keep on reading. cheers
12-09-2015 05:48 AM
Nothing there looks particularly difficult to achieve in PDF destination. Generally speaking though I find most of my outputs goto RTF destination (text markup language which Word can interpret), and then to PDF from them, but that's mainly a reporting requirement - although its worth noting that with RTF you can send out RTF markedup language as part of the report to alter things in the report.
Have a look at the above link, shows some of what can be done. Useful for adding underlines, subgroups and special elements.
12-09-2015 05:53 AM
I hadnt seen the above link -looks promising, thanks!
How do you convert from RTF to PDF? I've been using VBscript fo convert from XLS to PDF.
12-09-2015 06:08 AM
You can automate it with VBscript as before, basically open Word, open the file, print to pdf. The way I do it however is (presuming you have full adobe on your machine), highlight all the files you want to combine into PDF and right click and Choose "Cobine files in acrobat". This will open a dialog where you can move things up and down in the order, and apply bookmarks. Pretty straightforward.
12-09-2015 08:32 AM
No acrobat license handy, will stick with VBScript. Do you recommend using ODS Layout, or just plain proc report?
12-09-2015 08:47 AM
Never used ods layout myself, but if you need grided output then thats the way to go, my outputs tend to be one table per page/file.