Hi I have a dataset in sas with all the fields formatted the way I want but when I export to xlsx using the code below, I lose the formats. proc export data = dataset1 outfile = "...\dataset.xlsx" dbms = xlsx replace; sheet = final; run; Any way to keep the format? ... View more

When I run this, the format for field1 should be 12,346,756 but it's 12346756 data test;    infile datalines DLM = ',';    input   field1      : comma10.0   lossyr                   : 20. ;     DATALINES ; 12346756,2014 ; ... View more

IS it possible to merge and then do other procedures like creating variables etc. data help; merge data1(in=a) data 2; by key; if a; newfield=field1+field2; run; not working for me ... View more

The ods _all_ close; ods tagsets.ExcelXP path='\\Netapp4\Sasdata\Risk Modelling\CL Fleet\ROI\2014\00 - Main DWH\Enhanced DWH' file='Claims_dists.xml' style=Printer; %macro sheets(var); ods tagsets.ExcelXP options(sheet_name="&var"); proc print data=&VAR._DIST noobs style(Header)=[just=center]; run; quit; %mend; %sheets(OWN); %sheets(TPI); ods tagsets.ExcelXP close; ... View more

I'm trying to convert xml files to xlsx in sas. This link shows how to do it: http://support.sas.com/kb/43/496.html Using the code in the link and setting up the same folder names(i.e. c:\temp ...), the program works perfectly. However, when I change the directory it doesn't work. The log doesn't seem to display errors but a black box appears for a second after the program is run. Here is the code I am using: ods noresults; proc sort data=sashelp.class out=test;  by age; run; ods tagsets.excelxp file="S:\Matthew\SAS\XML to Excel\convert\temp.xml" newfile=bygroup; proc print data=test;  by age; run; ods tagsets.excelxp close; options noxsync noxwait; filename convert 'S:\Matthew\SAS\XML to Excel\sasmacro' ; %include convert(convert.sas); %convert_files(default=S:\Matthew\SAS\XML to Excel\convert,store=S:\Matthew\SAS\XML to Excel\temp.vbs,ext=xml); the convert.sas macro is below: %macro convert_files(default=S:\Matthew\SAS\XML to Excel\convert, store=S:\Matthew\SAS\XML to Excel\,ext=xml THE BELOW CODE IS IN THE LINK :- data _null_; file "&store"; put " Dim oXL "; put " Dim oFolder"; put " Dim aFile"; put " Dim FSO"; put " Set oXL = CreateObject(""Excel.Application"")"; put " Set FSO = CreateObject(""Scripting.FileSystemObject"")"; put " oXL.DefaultFilePath = ""&default"""; put " oXL.DisplayAlerts = False"; put "  if FSO.FolderExists(oXL.DefaultFilePath) then"; put "   Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)"; put "     For each aFile in oFolder.Files "; put "       If Right(LCase(aFile.Name), 4) = "".&ext"" Then"; put "        oXL.Workbooks.Open(aFile.Name)"; put "        oXL.Visible = false"; put "        if (oXL.Version) >= 12 Then" ; put "        oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xlsx"",51"; put "    Else"; put "       oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) - 4) & "".xls"",-4143"; put "   End If"; put " oXL.ActiveWorkBook.Close SaveChanges = True"; put " End If"; put " Next"; put " Set oFolder = Nothing"; put " end if"; put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly"; put " oXL.DisplayAlerts = True"; put " oXL.Quit"; put " Set oXL = Nothing"; call system("&store"); run; %mend; ... View more