topic Re: Code to create MED DEV report required by EU in ODS and Base Reporting

Code to create MED DEV report required by EU

sschleede — Tue, 23 May 2017 16:37:31 GMT

Our clinical study is required to do Medical Device reporting to be compliant with the EU DIRECTIVES 90/385/EEC AND 93/42/EEC. We need to create a MED DEV report weekly in Excel. I have attached a picture of the template. The requirements are that the column widths, fonts and coloring have to match the template. I believe we also need to color code some columns to highlight modified records from new records.

I know there are lots of resources in the community about creating reports in Excel, but has anyone specifically programmed the MED DEV report? I am not looking for how to generate the dataset. I am looking for the code to produce the Excel report from the dataset. This just seems like a task that many of us will have to do and it would be nice to have a common piece of code that is all debugged that we can reuse as a community. Or a nice SAS procedure (Proc MEDDEV?) that would create the report for us!

I will post my solution when I have it running and debugged.

I am not asking for anyone to create new code, but if you have already programmed this and are willing to share your code, that would be very nice!

Below is a picture of the MED DEV report in the Excel template:

Re: Code to create MED DEV report required by EU

ballardw — Tue, 23 May 2017 15:05:55 GMT

Many of the users are not going to open a Microsoft formatted document either because of policy, security blocking downloads or simple concern over other security issues such as occurred recently.

Convert the document to text if it contains descriptions.

Coloring and column widths can be set inside Proc Print, Report or Tabulate code using either ODS tagsets.excelxp or ODS Excel options.

Re: Code to create MED DEV report required by EU

RW9 — Tue, 23 May 2017 16:15:50 GMT

Really, strange I have never heard of this. Anyways, this community doesn't provide script library functions, nor would it be responsible for such a thing (these kind of libraries can take up vast amounts of peoples time and we are volunteers!). As for creating the specific output, I wouldn't download that file myself so can't see it, but most formatting and such like can be done in proc report, I would probably goto RTF, then convert the RTF file to docx if needed.

/* scrub this bit, I see your using .docx, aargh, even worse

with tagsets.excelxp. If they want exactly that file however, you may be better off dumping out the data to CSV and writing code in VBA within Excel to populate that report. Its about time that this kind of reporting and such like moves away from Excel as a format.

A final thought, you might check the Phuse site, they have a few scripts on there.

Re: Code to create MED DEV report required by EU

sschleede — Tue, 23 May 2017 16:39:21 GMT

Thank you for the pointers. The template is Excel and I didn't want to post an Excel document. I figured out how to add a picture to the original post, so I removed the Word document with the picture.

I will look into the suggestions here - they give me a direction to take.

Re: Code to create MED DEV report required by EU

sschleede — Fri, 26 May 2017 12:50:17 GMT

This is how I solved this. I used the Poor/Rich SAS Users Proc Export (http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export) macro.

I have an Excel template with the color coding, font and date formatting. The code below uses the macro to copy the template and put the data in the right places in the report.

I also format all dates as 03/14/2001 with locale = English (U.S.). I found out that you choose a date format with an asterisk and it will use the locale information stored in the recipients computer to determine if it should be displayed mm/dd/yyyy or dd/mm/yyyy.

options missing = ' ';
/* This puts the main part of the report starting in cell A9 */
%exportxl(data=MedDev,
                outfile=&RepDir.\MedDev Report.xlsx ,
                sheet=Tabelle1,
                type=N,
                usenames=L,
                range=A9,
                replace=Y,
                template=&RepDir.\MedDev template.xltx ,
                templatesheet=Tabelle1,
                useformats=Y,
                method=I,
                server_path=);

/* This puts the run date into cell C8 */
%exportxl(data=RunD,
                outfile=&RepDir.\MedDev Report.xlsx ,
                sheet=Tabelle1,
                type=M,
                usenames=N,
                range=C8,
                replace=Y,
                template=,
                templatesheet=,
                useformats=Y,
                method=I,
                server_path=);