topic Re: Output/creating multiple excel documents - with preexisting excel template in ODS and Base Reporting

Output/creating multiple excel documents - with preexisting excel template

Geetu — Sun, 07 Aug 2016 04:33:06 GMT

Hi I'm SURE I can do this in SAS. This is SAS-9.4 and no, we don't have ACCESS to PC.
We have an excel-based tool we want to use to complete over 5,000 reviews. I need a way to populate ONE named tab and generate 5,000 copies of the tool - based on my sas data. Here's the code I'm using to generate 20 separate tabs within 1 excel document (as a test):

ods listing close; 
ods tagsets.ExcelXP file="testfile.xml" path="C:\testsas\" style=Statistical; 

proc print data=testset noobs label; 
by sampleid; 
var contractorID sampid linenumber UnitsBilled AmtPaid ; 
run; 

ods tagsets.ExcelXP close;

What I need instead is to insert the data from the SAS dataset a named tab in a specific excel document,
and generate as many excel documents as there are sampleids (i.e. the BY var).
So, I have an existing Excel document Tool.xls with 5 tabs. I want to put the SAS data into the tab
named "Sample" save the excel document with the new name Tool_1.xls ... repeat for the 5,000 by-vars.
Help! I'm a huge booster of SAS, and I want to prove we can do it very slickly with SAS. Please don't deflate my faith in SAS!
thank you!

Re: Output/creating multiple excel documents - with preexisting excel template

Reeza — Sun, 07 Aug 2016 04:37:19 GMT

Personally, I would switch to XLSX as a minimum.

I would also purchase Access to PC because the cost savings versus extra time is a no brainer.

Here's an alternative option:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

If you want to get into the difficult arena, look into DDE, though you'll get the response it's not supported any longer. And that's true as well. However, it still works.

This doesn't quite stand alone as a presentation, but it does have some sample code.

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf

You do need to be working on a desktop installation (vs server) for DDE to work and have X command enabled. This allows you to copy the template and save as a new copy X 5000 times. Then you export to the new copy and voila, you're done. Your process sounds relatively simple.

Good Luck.

Re: Output/creating multiple excel documents - with preexisting excel template

Geetu — Sun, 07 Aug 2016 23:33:00 GMT

Thank you Reeza!

I appreciate the guidance. I'll look into getting the company to spring for Access to PC, but as usual, the current problem needs solving yesterday! 🙂

I'll review the information you sent, work on the problem tonight, and report back.

Re: Output/creating multiple excel documents - with preexisting excel template

Geetu — Mon, 08 Aug 2016 12:29:52 GMT

Argh! So close!!

First of all the Exportxl Macro provided in the paper you linked works. Yay! It rocks actually, and I can see using it a lot in future.

I ran my test dataset of 20 records - specifying the Excel document that has all the multiple tabs I need.

The 20 records were put into the specified tab in that Excel document ... slick as anything.

I'd still need to manipulate the data to export records based on the "BY" variable .. but I thought I could write a macro for that.

However, the problem is that the Exportxl macro requires that the resulting Excel document is already created and named ... which means I still need to create 1000s of the initial Excel workbooks which then can have the data inserted by this macro.

Back to researching ...

Re: Output/creating multiple excel documents - with preexisting excel template

Reeza — Mon, 08 Aug 2016 12:52:43 GMT

The second link covers that.

X copy ...

Re: Output/creating multiple excel documents - with preexisting excel template

Geetu — Mon, 08 Aug 2016 13:25:04 GMT

Thanks Reeza ... I'm now digging into x copy and figuring it out.

I promise to post my solution here when I'm done.

cheers!

geetu

Re: Output/creating multiple excel documents - with preexisting excel template

Geetu — Mon, 15 Aug 2016 01:55:01 GMT

Thanks so much Reeza!

Your assistance led me to the right solution for making a copy of the starting Excel document and using the Macro to modify one of the worksheets. That worked great!

My challenge is to get it to work for 10,000 times ... I thought of using a Macro ... which would still mean I'd have to run 10,000 lines but I can't get a macro to work within the %exportxl macro.

Here's what I've done:

%macro Tool(ID);

data tool1;
set testOxy ;
if sample_ID = "&id";
format From_Date To_Date Billed_Date mmddyy10.;
run;

options noxwait noxsync;
%sysexec copy "U:\SMRT\Med Review Tool Pilot\Oxy Review Tool\OxygenMRPilot0811_d2.xlsx" 
     "C:\testsas\OxygenMRPilot_&ID..xlsx" ; 
run;
%MEND;

%Macro tool2(ID2);

%exportxl( data=tool1 ,
   outfile= C:\testsas\OxygenMRPilot_&ID2..xlsx,
   Sheet=SMRT,
   Type=M,
   Replace=N,
   useformats=Y);
run;
%MEND Tool2;

%tool(T1-OX3-3081);
%tool2(T1-OX3-3081);

%tool(T1-OX3-3090);
%tool2(T1-OX3-3090);

I know it's clunky and not very elegant ... but it works.

Is someone can think of more elegant way to run the code 10,000 times (instead of my having to invoke the two Macros 10,000 times, I'd be super happy. The trick is I need to use the "ID" for naming the resulting files, and not just consequtive numbers.

thank you

Re: Output/creating multiple excel documents - with preexisting excel template

Reeza — Mon, 15 Aug 2016 02:23:55 GMT

Did you look into call execute?

Re: Output/creating multiple excel documents - with preexisting excel template

Reeza — Mon, 15 Aug 2016 02:27:07 GMT

I don't understand why you have two macros.

Why dont you remove the %mend and %macro in the middle, change ID2 to ID and you have a single macro to be executed 10000 times.

Re: Output/creating multiple excel documents - with preexisting excel template

Reeza — Mon, 15 Aug 2016 02:40:07 GMT

Options noxwait noxsync;
%macro Tool(ID);

%sysexec copy "U:\SMRT\Med Review Tool Pilot\Oxy Review Tool\OxygenMRPilot0811_d2.xlsx" 
     "C:\testsas\OxygenMRPilot_&ID..xlsx" ; 
run;


%exportxl( data=TestOxy (Where=(sample_ID="&ID")) ,
   outfile= C:\testsas\OxygenMRPilot_&ID..xlsx,
   Sheet=SMRT,
   Type=M,
   Replace=N,
   useformats=Y);
run;
%MEND;

Data _null_;
Set idlist;

Str = catt('%tool(', id, ');');
Call execute ( Str);

Run;

Re: Output/creating multiple excel documents - with preexisting excel template

Geetu — Mon, 15 Aug 2016 13:28:09 GMT

You know I did have just one Macro initially ... but it would fail. But I think I had it set up incorrectly in some way (which I now forget!).

But THANK YOU Reeza!!! You've been amazingly helpful and so kind with my slowly grasping the information you've provided. It's nice to re-grease my SAS wheels!

I love your elegent rewrite of my macro, but it doesn't work ... running that code gives me the same error I was seeing last night - the text of the macro is written into the excel document rather than run as code. Weird I know. I think it has to do with timing (?) .. that is, the copy procedure is simply copying over the text of the macro ... rather than the macro getting to execute (if that makes sense).

In any case, I re-did my Macro ... cleaning it up carefully, and used your tip of creating the _null_ dataset and a call execute to run multiples of IDs ... and voila! Got what I need ... with the testsets of 20 and 60 cases.

So here's my working code now:

%macro Tool(ID);

data tool1;
set testOxy ;
if sample_ID = "&id";
format From_Date To_Date Billed_Date mmddyy10.;
run;

options noxwait noxsync;
%sysexec copy "U:\SMRT\Med Review Tool Pilot\Oxy Review Tool\OxygenMRPilot0811_d2.xlsx" 
     "C:\testsas\Tests_0814\OxygenMRPilot_&ID..xlsx" ; 
run;
%exportxl( data=tool1 ,
   outfile= C:\testsas\Tests_0814\OxygenMRPilot_&ID..xlsx,
   Sheet=SMRT,
   Type=M,
   Replace=N,
   useformats=N);
run;
%MEND Tool;


Data _null_;
Set idlist;

Str = catt('%tool(', id, ');');
Call execute ( Str);

Run;
;

Only one odd result ... there's a stack of cmd windows open .. expected .. and they each slowly close as the run gets completed. However, the final cmd window stays open till I manually close it. It doesn't seem to affect the results tho'