Thank you Oleg! It worked for me. Very helpful. ... View more

Thank you Iassen. Appreciate it. ... View more

And, of course, within SAS one can also always build and run VBA and/or DDE code to accomplish such tasks. ... View more

You will need to use FILENAME with the "PIPES" option to read a directory and then use FDELETE to delete individual files; I don't expect FDELETE supports any wild-carding. And, also, yes SAS supports FTP, using FILENAME with the FTP option. Check your SAS companion guide for details. Also, the SAS support http://support.sas.com/ website has information about these techniques. Scott Barry SBBWorks, Inc. ... View more

Hi.Cynthia. I learned that using DDE also can be without opening xls file. SAS is a really amazing tool. ... View more

You can use tagsets.excelXP to accomplish most of that, but it depends more on what you're trying to do. Is this an already created spreadsheet that you need to format or are you looking to control your output more? ... View more

Hi: I agree with Scott -- knowing what is meant by "drop it into the Mainframe" is critical here. If, for example, you have SAS on a local machine and you have SAS on the Mainframe and you have SAS/Connect, then you could read the Excel file, with the new columns on your local machine and then create a SAS dataset on the Mainframe which contained the new information. (This is similar, but not entirely similar to what would happen if you had EG on a local machine and were communicating with SAS on the Mainframe.) If you only have SAS on a local machine, then you have different options. This is where the "drop it into the Mainframe" becomes critical. Mainframe files are not like PC files. They can live in proprietary database formats, like Oracle or DB2 or Adabas. A Mainframe file can be sequential -- it can be read with any program on the mainframe or a Mainframe file can be a a file that lives in a Partitioned Data Set; or a Mainframe file could be an older file type like VSAM or ISAM or BSAM format. PC files are in ASCII form in internal storage; Mainframe files are in EBCDIC form in internal storage. Last, but certainly not least, Excel does not live on the Mainframe. So what type of file would you create that would be "dropped" into the Mainframe??? A SAS dataset? A flat file to be read with some other type of program? A database file? What database? What you want to do is possible. SAS on a local machine could create a sequential file that could be FTP'd to a Mainframe, and usually the FTP process will automatically do the translation from ASCII to EBCDIC. However, once on the Mainframe, what happens to the file? What program will read the file? Where does the file go? Where is the file stored? Generally speaking, SAS would not be able to create a file for a proprietary file format (like Oracle, DB2, VSAM, etc) without having SAS on the Mainframe -and- the appropriate SAS/ACCESS product. Just as you need SAS/ACCESS for PC Files to read Excel files on the Windows platform, you would need SAS/ACCESS for the appropriate file type or DBMS in order to WRITE files in a proprietary format. Conceptually, what you want to do is possible. Your best resource, at this point, would be to contact Tech Support with this question and they could look at the current suite of SAS products that you have on various Platforms and they might be able to narrow down your options for making this concept a reality. For example, if you do have SAS and SAS/Connect on your Mainframe, then they can point you to examples of programs that use SAS/Connect. If you do not have SAS on the Mainframe, then they can point you to examples of programs that create "flat files" suitable for FTP'ing. cynthia ... View more

Hi Cynthia, Thank you so much on this one. I ended up on using SCAN 🙂 Much appreciated. ... View more

Yennie A SAS table is not the same like an Excel spreadsheet. Adding empty rows would mean observations with all variables set to missings to the SAS table. This would influence all further calculations. What you need is one SAS table (dataset) with all the data from table A and table B. 1. Use a datastep or PROC TRANSPOSE to bring dataset A into the structure of dataset B. 2. Create a new dataset C by combining A and B (add a variable identifying where the data comes from). 3. Use something like PROC REPORT to create the desired output (Proc Report allows you to add empty lines between by groups). HTH Patrick Message was edited by: Patrick ... View more

Hi Yennie Some questions and ideas. Q: are the values for fromdate and todate really strings or are these SAS date values with a SAS date format assigned? You can easily check this by running a PROC CONTENTS. Q: Your example data shows the same dates for fromdate and todate. Is this how your data really looks like? And would that mean that these 2 cases wouldn't be counted anywhere? Q: What happens if a risk period (fromdate to todate) goes over a monthend. Should this risk be counted in 2 months? Or only when it started? Forget about using any macro code. It won't be needed. What most likely has to be done is creating some additional observations (i.e. one obs per month where the risk exists) and then a PROC TABULATE with an appropriate format applied to the variable containing the date. Have also a look at functions like INTCK() and INTNX(). Please provide also some "real" sample data, best in form of a data step creating a SAS table. HTH Patrick Message was edited by: Patrick ... View more

Yennie - when I run the code I provided, it produces both a png file and an htm file, such as ... login003> ls -lt foo* -rwxr-xr-x 1 realliso isd 3172 Mar 24 08:11 foo.htm -rwxr-xr-x 1 realliso isd 7569 Mar 24 08:11 foo.png Try running the code from Windows SAS, as-is (with no changes), from a fresh SAS session. Oh, and not from EG. ... and I bet it will produce both files for you 🙂 With that as your starting place, then try adding your own SAS/Graph proc, and make sure it still produces both files. And then try running it from EG, etc, etc. At each point, making sure it produces both files. Perhaps your SAS session has a setting that's overriding the code, or maybe something similar if you're running from EG. ... View more

ratios in Tabulate? no problem! (well .. almost no problem) Just use pctsum on analysis variable for numerator and point denominator definition to the denominator of the ratio. After that, you just need to tame the tabulate tendancy to present the result as percentage points with 10% as 10 and not .10, but proc format solves that too! [pre]Proc format ; picture mnyR(round) other = '00,000,001.23'( mult=1 prefix='$' ) ; run;[/pre] * seems it needs that MULT=1 !;[pre]Proc Tabulate Data= SummaryTable ; Class FRUITS ; Var claimCount epy incurToDate earnedPrem ; Table ALL FRUITS , SUM=' '*claimCount SUM=' '*epy claimCount=' '*pctsum ='claim freq' *f= mnyR. ; Run ; [/pre] not quite sure why it is claimCount/epy, but hope the description above will clarify alternative ratios. PeterC forgot to add the $ Message was edited by: Peter.C ... View more

Hey Scott... you were right. it was due to the memname. thanks heaps and millions! cheers, yennie ... View more

Hi Scott, The ODS and "Next" are within my macro script so it really didn't matter whether if it's a "Next" link or a "Back" link. A simplified code as below. (Real simple I know) Hope it didn't look silly to you! Thank you guys so much for your awesome help! 🙂 ************************************************************* %Macro html_page ( output_html=, name_html= ) ODS HTML Body = &output_html; TITLE; FOOTNOTE Color=Black Justify=right link="&name_html." "[View Cancellation Results]"; PROC PRINT DATA=Fruit NOOBS LABEL; VAR sales earnings days; BY Month RUN;QUIT; Title; FOOTNOTE; GOPTIONS RESET=ALL; ODS HTML Close; %Mend html_page ; %html_page (output_html=renewalA.html , name_html=cancellationA.html) %html_page (output_html=renewalB.html , name_html=cancellationB.html) %html_page (output_html=renewalC.html , name_html=cancellationC.html) ************************************************************* ... View more

Hi Cynthia! I haven given this a try and this works awesome! Cheers, Yennie ... View more