LOL!!  That's it!!  <DUH!>   Sigh ... reminds me of when I first started using SAS 20 years ago and would forget a semi-colon! I've been away from SAS for 2 years  ... one gets rusty, but it's nice to know this dinosaur can still learn some new tricks!   thanks again Reeza, and everyone else here to helped!   Love the SAS community! ... View more

Thanks Linus .. I did debug and test the Macro independently; my other responses here should help clarify why I think it's the CATT function.   Thanks for helping ...and I love your tag line! ... View more

Hi Ballardw ... here's a sample of the IDlist .. nothing that should be a problem   ID ID2 T1-OX3-3007 S3007 T1-OX3-3010 S3010 T1-OX3-3081 S3081 T1-OX3-3083 S3083 T1-OX3-3087 S3087 thanks for helping ... View more

Thanks everyone. I did use SYMBOLGEN option so I could see how the macro was resolving. Yeah, I agree, there's a comma missing ... however, when I tried to add one - here's the code & error message:   952 Str = catt('%tool1(', id,"," Id2, ');'); --- 22 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=. The reason I think I'm just not getting some nuance of CATT is that if I use the old method of concatenating (I'm a dinosaur): Str2 = '%tool1(' || trim(id) || ',' || trim(IdN) || ');' ; I get what I need.  I should be content LOL .. but it's bugging me that CATT won't work here.   thanks all. ... View more

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' ... View more

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

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

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

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