About SteveNZ

SteveNZ · ‎12-09-2013

Try (untested): No need to set the macro each time. Also do your users realise they're opening an XML file and not XLS? You can give it a xls extension instead or convert it to excel using VB script or similar. DATA _NULL_; SET A ; CALL SYMPUT('DS'||LEFT(_N_),TRIM(MEMNAME)); CALL SYMPUT('TOTAL',LEFT(_N_)); RUN; %macro libdata ; %DO i=1 %TO &total; ods tagsets.ExcelXP options(sheet_name="&&DS&I" width_fudge='0.50''landscape' row_repeat='1-2'); proc print data=conv.&&DS&I label noobs; run; quit; %END; %mend libdata ; ods _all_ close; ods tagsets.excelxp file='D:\program\DataTransfers\xls\SASDATA_02DEC2013.xml' style= Printer; %libdata ; Ods tagsets.excelxp close;

SteveNZ · ‎12-08-2013

Here's the code I use and it works perfectly fine in Outlook: ods _all_ close; filename mymail email from="YOUREMAIL@SOMEWHERE.COM" to="RECIPIENT@SOMEWHERE.COM" cc="CC_IF_REQUIRED@SOMEWHERE.COM" subject="Your E-mails Title" content_type="text/html"; ods msoffice2k file=mymail style = sasweb ; *proc report code below...; proc report data = ..... run ; ods msoffice2k close;

SteveNZ · ‎12-05-2013

Depends if number is stored as SAS date with a format on it. If it's stored as a character string then: data want ; length day month $2 year $4 ; set have ; day = scan(date,2,'/') ; month = scan(date,1,'/') ; year = scan(date,3,'/') ; run ; If as a date then: data want ; set have ; day = day(date) ; month = month(date) ; year = year(date) ; run ; Similarly: data want ; set have ; hour = hour(time) ; minute = minute(time) ; run ; data want ; length hour minute $2 ; set have ; hour = scan(time,1,':') ; minute = scan(time,2,':') ; run ;

SteveNZ · ‎11-25-2013

Hiya, Try: data have ; length col1 col2 $3 ; infile datalines delimiter=','; input col1 $ col2 $ ; datalines; abc,def abc,xyz rst,xyz ; proc sort data = have ; by col1 col2 ; run ; data want ; set have ; by col1 col2 ; retain col1_summary ; if first.col1 then col1_summary = catx(',',col2) ; else col1_summary = catx(',',col1_summary,col2) ; if last.col1 then output ; run ; Repeat above logic to get col2 summary. cheers

SteveNZ · ‎11-14-2013

This works but not too elegant: data visual_arts ; set have (where = (dept = 'Visual Arts')) ; call symput('dlen',length(dept)) ; run ; data visual_arts (drop = dept) ; length department $&dlen ; set visual_arts ; department = dept ; run ;

SteveNZ · ‎10-22-2013

Anyone?

SteveNZ · ‎10-17-2013

Hi all, Does anyone have any code or direct me to a paper that allows for me to produce an RTF file that uses Word 2010's table of contents? I've found code that produces a TOC at the top of the page but a) you have to update field it to get it to populate and b) it whacks all sorts of special characters into the reports. I actually don't want this option at all, I just want to use Words navigation. Has anyone cracked this? I see an option WORDSTYLE was available in earlier versions but cant find it in 9.3 documentation. Many thanks in advance Steve

SteveNZ · ‎10-03-2013

You could do this: data want ; length colour $6 ; set have ; if var1 < 0 then varind1 = -1 ; else varind1 = 1 ; if var2 < 0 then varind2 = -1 ; else varind1 = 1 ; if var3 < 0 then varind3 = -1 ; else varind1 = 1 ; varscore = sum(varind1,varind2,varind3) ; if varscore = -3 then colour = 'Red' ; else if varscore = -1 then colour = 'Yellow' ; else if varscore = 1 then colour = 'Blue' ; else colour = 'Green' ; run ; Then use the colours in a format to traffic light the proc report.

SteveNZ · ‎10-02-2013

Yep, Wendi Wrights paper shows you how: http://www2.sas.com/proceedings/forum2007/095-2007.pdf

SteveNZ · ‎09-23-2013

You could change your character dates to actual SAS dates and then format them using the MONNAME. format. 52 data _null_ ; 53 format date monname. ; 54 date = '01jan2013'd ; 55 put date ; 56 run ; January

SteveNZ · ‎09-16-2013

Hiya, Create the following macros using your &date one to subset: data _null_ ; call symput('fstart',intnx('year.4',&date,0,'b')) ; call symput('fend',intnx('year.4',&date,1,'b')-1) ; run ; cheers Steve

SteveNZ · ‎09-05-2013

Hiya, The process I use with EG is the following - its a pain to set up but you only have to do it once. The excelxp tagset gives you a lot of control over output especially if you have custom formats. Download and install Chris@SAS custom tasks and the copy files to/from server from his SAS for Dummys website Create a folder on your server for xml files Create XML files in your code using the tagsets.excelxp (as many as you want) as well as the vbs script included below into the folder from step 2 - the vbs script will convert ALL your xml files to native Excel ones once downloaded Use the copy files task you've installed to get your xml files and the vbs file (use *.* in the dialogue to get both xml and vbs files) from the folder in step 2 to a local folder you have set up Use the 'System Command' task from Chris@Sas to start the vbs file off eg: "C:\MYLOCAL\convert.vbs" (no quotes and filename must be in dos format ie longer names abbreviated with ~1). The vbs file is set to self-destruct I then have another step that deletes the xml files and vb script on the server as you no longer need them The code for the vb script I use is below. It takes all xml files in the folder and converts them to excel so use with care... I have Excel 2010, if you have another version you may have to change the xlNormal number from 51 to your version *vb script to convert xml and then self destruct (delete); data _null_ ; file "/YOURSERVERFOLDER/convert.vbs" ; put "Dim xlApp, xlWkb, SourceFolder, TargetFolder, file" ; put "Set xlApp = CreateObject(""excel.application"") " ; put "set fs = CreateObject(""Scripting.FileSystemObject"")" ; put "Const xlNormal=51" ; put "SourceFolder=""C:\MYLOCAL"" " ; put "xlApp.DisplayAlerts = False " ; put "xlApp.Visible = false" ; put "for each file in fs.GetFolder(SourceFolder).files" ; put "If Right(LCase(file.Name), 4) = "".xml"" Then"; put " Set xlWkb = xlApp.Workbooks.Open(file)" ; put " BaseName = fs.getbasename(file)" ; put " FullTargetPath = SourceFolder & ""\"" & BaseName" ; put " xlWkb.SaveAs FullTargetPath, xlNormal" ; put " xlWkb.close" ; put "end if" ; put "next" ; put "fs.DeleteFile(""C:\MYLOCAL\*.xml""), DeleteReadOnly"; put "xlApp.DisplayAlerts = true" ; put "Set xlWkb = Nothing" ; put "xlapp=Quit"; put "Set xlApp = Nothing" ; put "fs.DeleteFile(Wscript.ScriptFullName)" ; put "Set fs = Nothing" ; run;

SteveNZ · ‎06-26-2013

Thanks Chris, I have it working to a degree now. I've got the xml files being created on the server, being downloaded and converted to native excel files via the two tasks. I see I can also download using the wildcard so all good. I've just got to alter my vbscript to delete the xml file once saved as but that shouldn't be a problem; my current code does this via SAS code. For anyone else who has problems with the dlls being blocked simply copy them to a flash drive formatted fat32 and then back again and voila the problem is gone. I kid you not. cheers Steve

SteveNZ · ‎06-26-2013

Found the answer on your site Chris: http://blogs.sas.com/content/sasdummy/2013/05/19/unblocking-custom-task-dlls/ Just have to find a friendly IT admin to unblock the Dlls.... If you could let me know whether the tasks handle the dynamically created files though that would be great. cheers Steve

SteveNZ · ‎06-26-2013

Hi Chris, Yes I am using ExcelXP to write the XML and then a VB script to convert. I tried to install the tasks above but getting errors. Is this because I'm running EG5.1? If so do you have a 5.1 version? One other question I have is will the above solution cope with dynamically named files? Currently users enter as many client ids into the program as they need and hit go and it spits out an excel file per client eg 'Client Profile for ID 123456789.xls'

Online Status	Offline
Date Last Visited	‎01-14-2025 07:21 PM

Re: SAS VA filter data based on the USERID the user logs in with

Re: SAS VA filter data based on the USERID the user logs in with

SAS VA filter data based on the USERID the user logs in with

Re: Create a Download Link For Dynamic PDF File

Create a Download Link For Dynamic PDF File

Re: Use Report Level Filters in Dynamic Titles

Re: Use Report Level Filters in Dynamic Titles

Re: Use Report Level Filters in Dynamic Titles

Re: Use Report Level Filters in Dynamic Titles

Re: Use Report Level Filters in Dynamic Titles

Re: Code Going Missing

Code Going Missing

Lines Wrapping in ODS Excel

Re: Concatenate multiple rows into a single value

Re: Which day of the week has highest number of accidents?

Re: Error opening exel file

Re: Help concerning mail generated from SAS

Re: Parsing a Date/Time

Re: how to combine multiple observations into one

Re: Trimming a Character variable

Re: 9.3 RTF Code for TOC That Works in Word 2010

9.3 RTF Code for TOC That Works in Word 2010

Re: Characterizing rows with colurs

Re: specifying column width for proc tabulate

Re: Proc gchart

Re: Filter by current fiscal year using a Macro

Re: How to export from SAS to Excel?

Re: Is Base SAS Required to Process Files Locally with EG?

Re: Is Base SAS Required to Process Files Locally with EG?

Re: Is Base SAS Required to Process Files Locally with EG?