The SAS Output Delivery System and reporting techniques

Output/creating multiple excel documents - with preexisting excel template

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Output/creating multiple excel documents - with preexisting excel template

[ Edited ]

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!


Accepted Solutions
Solution
‎08-16-2016 01:22 PM
Super User
Posts: 19,063

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

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/FareezaKhu...

 

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. 

View solution in original post


All Replies
Solution
‎08-16-2016 01:22 PM
Super User
Posts: 19,063

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

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/FareezaKhu...

 

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. 

Occasional Contributor
Posts: 12

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

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!  Smiley Happy

 

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

 

 

 

Occasional Contributor
Posts: 12

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

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 ... 

 

 

 

 

 

 

Super User
Posts: 19,063

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

The second link covers that. 

X copy ...

Occasional Contributor
Posts: 12

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

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

Occasional Contributor
Posts: 12

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

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

Super User
Posts: 19,063

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

Did you look into call execute?

Super User
Posts: 19,063

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

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;
Super User
Posts: 19,063

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

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. 

Occasional Contributor
Posts: 12

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

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'

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 427 views
  • 2 likes
  • 2 in conversation