Graphing NONMEM output with EXCEL Macro

I saw a questions recently about graphics with NONMEM. I have an excel macro to make charts, divided by individual from NONMEM output that I would be happy to share. Basically, it uses the parse command to put the data into columns then the offset to select data for each individual. It wouldn't work well as email, since there are many variables defined. But I'll send it to anyone (unsupported of course) who sends a disc to Mark Sale 3900 Reservoid Rd NW Room NE403 Department of Pharmacology Georgetown University Washington DC 20007 or, if you want an encoded binary by email, send me mail to see if we have compatible encoding software. It may be helpful to those also working on a PC. Mark msale01@gumedlib.dml.georgetown.edu Follow-up by msale01@gumedlib.dml.georgetown.edu (Mark Sales) Dear Users, The response to my offer of the EXCEL (Yes EXCEL, not SAS) macro has been overwhelming. This macro makes an EXCEL graph of the data file output from NONMEM ($TABLE FILE =). It isn't necessary to use NOHEADER since this macro will delete any lines starting with text. But, I suggest that you only put 3 items in the Table, the first of which should be the id. This macro plots each id seperatly, on one plot and labels the first and last datum with the id number. That is, a data set with 30 individuals will yield a plot with 30 lines on it. The second data item will end up the x axis and the third the y axis coordinate. Additional items, if not deleted in EXCEL cause problems. I haven't included a smoother, since I don't (yet) have one that is better than the 2 available in EXCEL (moving average and an exponential smoother). Instead of sending it to all as encoded binary, I'm sending it over nmusers as text with instructions for defining the 5 functions that need to be defined. I hope this works: There are 90 lines in this macro. The first 87 lines have only one column. Lines 88-90 have 7 columns. I hope the tabs come through in lines 88 - 90. If not they will have to be put in. Copy the text into a macro sheet in A1. Five function need to be defined. To define a function, select Formula- define name. Then type in the function name, and the cell locations. You can use any name you like (and a control- key) for the main function, appearing in A13. The other four function need the correct name, which appears in that cell. For all function, the command box must be checked. A13 define as command function, any name you like A41 define as command function, delete_text A53 define as command function, delzero A65 define as command function, make_chart A74 define as command function, put Again, the id must be in column 1, x coordinate in column 2, y in column 3 and nothing beyond that. I have found this to be reasonable fast (500 data from 137 individuals in 11 second on a Pentium) My Pentium with 16 Mbytes ran out of memory at about 600 data. In general it took about as long to parse the data as to make the chart. To parse the data, open the add-in flatfile.xla and use smart-parse, with the Remove extra blank spaces. The next letter will be the macro text. Macro Text "readme: To use this macro, the data must be parsed into columns" "To parse data, use the parse or the smart parse command (in flatfile.xla)" "Id number in column 1, x axis in column 2, y in column 3" all lines with text will be deleted. "if you chose to, all lines with 0 in column 2 (i.e., doses)" will also be deleted from the data set. "To use this macro, define the following cells as commands (formula,define nam )" "define A13 to be a command (any name, cntrl key if you like)" "define A41 to be delete_text, a command" "define A53 to be delzero, a command" "define A65 to be make_chart, a command" "define A74 to be put, a command" make_nonmem_chart() =ECHO(FALSE) "=ALERT(""Warning, data set will be changed, lines with text will be deleted!" ,1,1)" =DIALOG.BOX(A88:G90) "=SET.NAME(""data"",GET.DOCUMENT(1))" =delete_text() "=IF(G89=TRUE,delzero())" =make_chart() "=SET.NAME(""curr_id"",!A1)" "=SET.NAME(""which"",1)" "=SET.NAME(""ids"",0)" "=SET.NAME(""total_dat"",0)" "=IF(ISBLANK(OFFSET(!A1,1,0)))" "=ALERT(""Please delete first blank column (or run smart parse with """"Remove extra blank spaces"""" checked)"",1,)" =RETURN() =END.IF() "=WHILE(ISNUMBER(OFFSET(!A1,total_dat,0)))" "=WHILE(OFFSET(!A1,total_dat,0)=curr_id)" ids=ids+1 total_dat=total_dat+1 =NEXT() =put() =NEXT() =ACTIVATE(chart) "=SELECT(""S1"")" "=PATTERNS(1,1,1,1,1,1,1,3,TRUE)" =RETURN() delete_text "=SET.NAME(""position"",0)" "=WHILE(NOT(ISBLANK(OFFSET(!A1,position,0))))" "=IF(ISTEXT(OFFSET(!A1,position,0)))" "=SELECT(OFFSET(!A1,position,0,1,6))" =EDIT.DELETE(2) position=position-1 =END.IF() position=position+1 =NEXT() =RETURN() delzero "=SET.NAME(""position"",0)" "=WHILE(ISNUMBER(OFFSET(!A1,position,0)))" "=IF(OFFSET(!A1,position,1)=0)" "=SELECT(OFFSET(!A1,position,0,1,6))" =EDIT.DELETE(2) position=position-1 =END.IF() position=position+1 =NEXT() =RETURN() make_chart =SELECT(!I1) "=NEW(2,2)" "=SET.NAME(""chart"",GET.DOCUMENT(1))" "=GALLERY.SCATTER(1,TRUE)" =ACTIVATE(data) =SELECT(!A1) =RETURN() put "=COPY(OFFSET(!B1,total_dat-ids,0,ids,3))" =ACTIVATE(chart) "=PASTE.SPECIAL(2,FALSE,TRUE,FALSE)" "=ATTACH.TEXT(4,which,1)" =FORMULA(curr_id) "=ATTACH.TEXT(4,which,ids)" =FORMULA(curr_id) "=SET.NAME(""which"",which+2)" =ACTIVATE(data) "=SET.NAME(""ids"",0)" "=SET.NAME(""curr_id"",OFFSET(!A1,total_dat,0))" =RETURN() 50 50 400 150 Delete Doses 13 50 70 400 60 delete rows with 0's in column 2? TRUE 1 30 30 50 30 OK Do not use the above without making the corrections mentioned below. Note the availability of this macro as an Excel file via WWW, gopher or ftp. Corrections from msale01@gumedlib.dml.georgetown.edu (Mark Sale) Sorry about the last message, my connection crashed. Last night I sent a graphic macro for EXCEL. Two errors occured in transmission. First, an extra line was added to the beginning. It should be deleted, the readme should be in cell A1. Second, lines 88 - 90 lost the tabs between columns. This should be spread out into 7 columns (A to G). Note that cell A88 should be blank, with a 13 in cell A89 and 1 in cell A 90. The only cell filled in column G should be G89, with TRUE. The text (delete Doses, delete rows with 0's in column 2 and OK) goes in column F.