The replies you have received so for don't seem to be answering the question. Your question as I understand it involves MS Office Automation more that SAS but you want a program that can be "called" with SAS to do the work. I have posted similar programs before but here it is again specifically for CSV.
/*input*/ filename workbook "C:\Documents and Settings\&sysuserid\My Documents\Book1.xls"; /*output*/ filename txtout "C:\Documents and Settings\&sysuserid\My Documents"; data Sheets(keep=indx csvpath); /* gather info */ length workbook txtout script filevar command $256; workbook = pathname('WORKBOOK'); txtout = pathname('TXTOUT'); script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs'); filevar = script; /* write the script */ file dummy1 filevar=filevar; put 'Const ' workbook=$quote256.; put 'Const ' txtout=$quote256.; put 'Const xlCSV = 6'; put 'Set objExcel = CreateObject("Excel.Application")'; put 'With objExcel'; put +3 '.Visible = False'; put +3 '.DisplayAlerts = False'; put +3 'Set objWorkbook = .Workbooks.Open(workbook)'; put +3 'i = 0'; put +3 'Set colSheets = .WorkSheets'; put +3 'For Each objSheet In colSheets'; put +6 'i = i + 1'; put +6 'WScript.echo i & " " & txtout & "" & objsheet.name & ".csv"'; put +6 'objSheet.SaveAs txtout & "" & objSheet.name & ".csv", xlCSV'; put +6 'Next'; put +3 '.Application.Quit'; put +3 'End With'; /* close the script file by opening another, not used */ filevar = catx('\',pathname('WORK'),'DUMMY.vbs'); file dummy1 filevar=filevar; /* look at the script, not necessary but may be useful */ infile dummy2 filevar=script end=eof; do while(not eof); input; putlog _infile_; end; /* call the script */ command = catx(' ','cscript',quote(strip(script)),'//nologo'); infile dummy3 pipe filevar=command end=eof truncover; do while(not eof); input indx csvPath $256.; output; putlog _infile_; end; stop;
filename workbook clear; filename txtout clear; proc print; run;
LIBNAME urlib 'C:Sale reportsSVsales.xls'; This will avail the sheets of sales.xls as individual members of urlib library in SAS. Step 2: Data _null_; set urlib.sheet1; file 'C:UsersSV file.ext ' dsd; Put var1--varN; Run; by.
Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!