BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Geetu
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

10 REPLIES 10
Reeza
Super User

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. 

Geetu
Obsidian | Level 7

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.

 

 

 

Geetu
Obsidian | Level 7

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

 

 

 

 

 

 

Reeza
Super User

The second link covers that. 

X copy ...

Geetu
Obsidian | Level 7

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

Geetu
Obsidian | Level 7

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

Reeza
Super User
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;
Reeza
Super User

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. 

Geetu
Obsidian | Level 7

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'

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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