Running commands in windows, space in directory and filename determined using macro variables

[morglum]

Hey all,

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)

data _null_;
command = '"c:\temp\space dir\script_copy_worksheets_to_workbook.vbs"';
call system(command);
run;

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";

data _null_;
command = '"copy &file_from. &file_to."';
call system(command);
run;

I have spent way too much time on this, any help would be greatly appreciated.

cheers

***************************************************************

script_copy_worksheets_to_workbook.vbs

***************************************************************

'~~> 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
Set wbsrc=objExcel.Workbooks.Open(Filename)
wbSrc.Sheets(1).Name = left(objFSO.GetFileName(objFile), Len(objFSO.GetFileName(objFile))-4)
wbSrc.Sheets(1).Copy objWorkbook.Sheets(objWorkbook.Sheets.Count)
wbSrc.Close
End If
Next
objWorkbook.Sheets("Feuil1").delete
objWorkbook.Sheets("Feuil2").delete
objWorkbook.Sheets("Feuil3").delete
'~~> Close and Cleanup

objWorkbook.SaveAs (strFileName)

objWorkbook.Close
objExcel.Quit

Set wbsrc=Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

[morglum]

I think I got  part of 3)  working based on Tim's post here:

https://communities.sas.com/t5/Base-SAS-Programming/resolving-macro-variable-inside-single-quotes/td...

%macro tesT(liste);
data test;
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 ||'"';
call system(command);
run;

%mend;
%test(4707);

[RW9]

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.

[morglum]

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.

[RW9]

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.

[morglum]

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

data test;
file_from= "filepath";
file_to= "filepath";
command = 'copy "' || file_from || '" "'|| file_to ||'"';
call system(command);

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_;
source_folder="sourcefolder";
destination="destinationcfile";
command = '"path\script_copy_worksheets_to_workbook.vbs" "' || source_folder || '" "' || destination || '"' ;
call system(command);
run;

run;

[morglum]

Hi RW9,
Thanks again for the long replies. I just came back from vacations and I still can't get this topic out of my head.

The only reason that I am using Excel is because I can make a "good looking" report in it.

You would rather not use Excel. What do you normally use? I'm giving Proc Report a hard look, but would like to hear about other possibilities.

[RW9]

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

[morglum]

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

[RW9]

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.  

http://www2.sas.com/proceedings/forum2007/151-2007.pdf

Have a look at the above link, shows some of what can be done.  Useful for adding underlines, subgroups and special elements.  

[morglum]

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.

Cheers

[RW9]

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.

[morglum]

Thanks!

No acrobat license handy, will stick with VBScript.  Do you recommend using ODS Layout, or just plain proc report?

cheers

[RW9]

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.