BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.

Hi Geeks:

I am trying to figure out 

How to combine 2 independent Excel files in One excel. (proc import won't be feasible since one of the excel book has color coded cell)

Have

Excel book ABC: Had Sheet A , Sheet B, Sheet C

Excel book Color 😧 Had color coded Sheet ColorD

Want:

Excel book all : Sheet A, Sheet B , Sheet C , Sheet ColorD

Question:

Is there a way to achieve the goal in SAS?

Please see attached EXCEL files 

Thank you all,

purple

 

1 ACCEPTED SOLUTION

Accepted Solutions
purpleclothlady
Pyrite | Level 9

Dear @ballardw @Quentin @SASKiwi :

 

Thank you  all the valuable inputs.

I figured out the puzzle😁, Since I didn't tell the whole information, here let me explain. It is all about how to play around with the ods 3 layers with macros. 

 

the coding logic is like this. 

Goal: I need to put dataset A into  excel book "colored" AFTER excel sheet "Color D" 

Solution: see the part3 . 

/*PART1:  Excel Book-A, B, C ....  */
/*-----------------$$MULTIPLE EXCEL BOOKS (3 EXCEL FILES A, B, AND C ) $$
 by using ODS EXCEL FILE= MULTIPLE times.--------------*/

%macro  mul(ds);
      /*1. SAS Steps to create data set A , B ,C*/
      data a;
      data b;
      data c;
%mend mul;
%mul(a);
%mul(b);
%mul(c);

/*2. ODS EXCEL Report to multiple Excel Books*/
ods results off;                       /*---TOP OUTLAYER ---*/    
ods listing close;
ods escapechar = '^';
				
*-----1. A-----; 					
ods excel file="C:\A.xlsx" ;            /*---MIDDLE LAYER 1---*/
 excel options (sheet_name = "A");
    proc report data=A ;
    run;
*-----2. B-----; ods excel file="C:\B.xlsx" ; /*---MIDDLE LAYER2 ---*/ excel options (sheet_name = "B"); proc report data=B ; run;
*-----3. C-----; ods excel file="C:\C.xlsx" ; /*---MIDDLE LAYER3 ---*/ excel options (sheet_name = "C"); proc report data=C ; run; ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing; /*PART 2: 1 Excel Book-Colored */ /*------ !! ONE excel book(COLORED) with MULTIPLE SHEETS!! Color D,Color E,Color F by putting the ODS EXCEL FILE = !OUT! of the %macro color ....--------------*/ ods results off; /*---TOP OUTLAYER ---*/ ods listing close; ods escapechar = '^'; ods excel file="c:\Colored.xlsx" ; %macro color (ds); /*---MIDDLE LAYER */ /*1. SAS Steps to create colorD*/ /*2. ODS EXCEL Report*/ %if &ds=ColorD %then %do; ods excel options (sheet_name = "Color D); proc report data=ColorD ; run; %end; %else %if &ds=ColorE %then %do; ods excel options (sheet_name = "Color E); proc report data=ColorE ; run; %end; %else %if &ds=ColorF %then %do; ods excel options (sheet_name = "Color F); proc report data=ColorF; run; %end; %mend; %color(ColorD); %color(ColorE); %color(ColorF); ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing;


/*****************SOLUTION*****************/ /*----PART3: Goal-OutPut Dataset A to Excel Book "Colored" AFTER sheet "Color D"------*/ /*Part1*/ %macro mul(ds); /*1. SAS Steps to create data set A , B ,C*/ data a; data b; data c; %mend mul; %mul(a); %mul(b); %mul(c); /*2. ODS EXCEL Report to multiple Excel Books*/ ods results off; /*---TOP OUTLAYER ---*/ ods listing close; ods escapechar = '^';
/*Comment: MOVE ods excel file="C:/A.xlsx" to part2 from part1*/
*-----2. B-----; ods excel file="C:\B.xlsx" ; /*---MIDDLE LAYER2 ---*/ excel options (sheet_name = "B"); proc report data=B ; run; *-----3. C-----; ods excel file="C:\C.xlsx" ; /*---MIDDLE LAYER3 ---*/ excel options (sheet_name = "C"); proc report data=C ; run; ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing; /*Part2*/ ods results off; /*---TOP OUTLAYER ---*/ ods listing close; ods escapechar = '^'; ods excel file="c:\Colored.xlsx" ; %macro color (ds); /*---MIDDLE LAYER */ /*1. SAS Steps to create colorD*/ /*2. ODS EXCEL Report*/ %if &ds=ColorD %then %do; ods excel options (sheet_name = "ColorD); proc report data=ColorD ; run; /*-------SOLUTION: MOVE ODS EXCEL file="C:\A.xlsx" TO HERE-1. A-------;*/ ods excel options (sheet_name = "A"); proc report data=A ; run; /*--------------END OF SOLUTIION--------------------*/ %end; %else %if &ds=ColorE %then %do; ods excel options (sheet_name = "Color E); proc report data=ColorE ; run; %end; %else %if &ds=ColorF %then %do; ods excel options (sheet_name = "Color F); proc report data=ColorF; run; %end; %mend; %color(ColorD); %color(ColorE); %color(ColorF); ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing;


 

 

View solution in original post

4 REPLIES 4
ballardw
Super User

This looks like a question for a Microsoft Excel site, not SAS.

Quentin
Super User

If you have Excel, and you want Excel, and aren't doing any calculations, I would leave SAS out of it.  If you insert SAS into the middle, you have to deal with SAS trying to decipher the meaning of the all the Excel madness.  

 

If you need to automate combining two Excel files into one file, think about VBA, PowerShell, etc.  See e.g. https://www.ablebits.com/office-addins-blog/merge-multiple-excel-files-into-one/

 

Of course if DDE is still a working thing, and you have PC SAS, then that might let you drive it via SAS, just for fun.

The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.
SASKiwi
PROC Star

Check the colour-coded column for an Excel formula to see if one is present to do the colours. If not then then colour-coding must have been done manually.

 

No doubt there is some business logic underpinning the colour coding. Do you know what that logic is? If there is a colour-coding formula then you should be able to work that out. Once you know the business logic then it is easy to reproduce in SAS if you have the data with which to build the logic.  

purpleclothlady
Pyrite | Level 9

Dear @ballardw @Quentin @SASKiwi :

 

Thank you  all the valuable inputs.

I figured out the puzzle😁, Since I didn't tell the whole information, here let me explain. It is all about how to play around with the ods 3 layers with macros. 

 

the coding logic is like this. 

Goal: I need to put dataset A into  excel book "colored" AFTER excel sheet "Color D" 

Solution: see the part3 . 

/*PART1:  Excel Book-A, B, C ....  */
/*-----------------$$MULTIPLE EXCEL BOOKS (3 EXCEL FILES A, B, AND C ) $$
 by using ODS EXCEL FILE= MULTIPLE times.--------------*/

%macro  mul(ds);
      /*1. SAS Steps to create data set A , B ,C*/
      data a;
      data b;
      data c;
%mend mul;
%mul(a);
%mul(b);
%mul(c);

/*2. ODS EXCEL Report to multiple Excel Books*/
ods results off;                       /*---TOP OUTLAYER ---*/    
ods listing close;
ods escapechar = '^';
				
*-----1. A-----; 					
ods excel file="C:\A.xlsx" ;            /*---MIDDLE LAYER 1---*/
 excel options (sheet_name = "A");
    proc report data=A ;
    run;
*-----2. B-----; ods excel file="C:\B.xlsx" ; /*---MIDDLE LAYER2 ---*/ excel options (sheet_name = "B"); proc report data=B ; run;
*-----3. C-----; ods excel file="C:\C.xlsx" ; /*---MIDDLE LAYER3 ---*/ excel options (sheet_name = "C"); proc report data=C ; run; ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing; /*PART 2: 1 Excel Book-Colored */ /*------ !! ONE excel book(COLORED) with MULTIPLE SHEETS!! Color D,Color E,Color F by putting the ODS EXCEL FILE = !OUT! of the %macro color ....--------------*/ ods results off; /*---TOP OUTLAYER ---*/ ods listing close; ods escapechar = '^'; ods excel file="c:\Colored.xlsx" ; %macro color (ds); /*---MIDDLE LAYER */ /*1. SAS Steps to create colorD*/ /*2. ODS EXCEL Report*/ %if &ds=ColorD %then %do; ods excel options (sheet_name = "Color D); proc report data=ColorD ; run; %end; %else %if &ds=ColorE %then %do; ods excel options (sheet_name = "Color E); proc report data=ColorE ; run; %end; %else %if &ds=ColorF %then %do; ods excel options (sheet_name = "Color F); proc report data=ColorF; run; %end; %mend; %color(ColorD); %color(ColorE); %color(ColorF); ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing;


/*****************SOLUTION*****************/ /*----PART3: Goal-OutPut Dataset A to Excel Book "Colored" AFTER sheet "Color D"------*/ /*Part1*/ %macro mul(ds); /*1. SAS Steps to create data set A , B ,C*/ data a; data b; data c; %mend mul; %mul(a); %mul(b); %mul(c); /*2. ODS EXCEL Report to multiple Excel Books*/ ods results off; /*---TOP OUTLAYER ---*/ ods listing close; ods escapechar = '^';
/*Comment: MOVE ods excel file="C:/A.xlsx" to part2 from part1*/
*-----2. B-----; ods excel file="C:\B.xlsx" ; /*---MIDDLE LAYER2 ---*/ excel options (sheet_name = "B"); proc report data=B ; run; *-----3. C-----; ods excel file="C:\C.xlsx" ; /*---MIDDLE LAYER3 ---*/ excel options (sheet_name = "C"); proc report data=C ; run; ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing; /*Part2*/ ods results off; /*---TOP OUTLAYER ---*/ ods listing close; ods escapechar = '^'; ods excel file="c:\Colored.xlsx" ; %macro color (ds); /*---MIDDLE LAYER */ /*1. SAS Steps to create colorD*/ /*2. ODS EXCEL Report*/ %if &ds=ColorD %then %do; ods excel options (sheet_name = "ColorD); proc report data=ColorD ; run; /*-------SOLUTION: MOVE ODS EXCEL file="C:\A.xlsx" TO HERE-1. A-------;*/ ods excel options (sheet_name = "A"); proc report data=A ; run; /*--------------END OF SOLUTIION--------------------*/ %end; %else %if &ds=ColorE %then %do; ods excel options (sheet_name = "Color E); proc report data=ColorE ; run; %end; %else %if &ds=ColorF %then %do; ods excel options (sheet_name = "Color F); proc report data=ColorF; run; %end; %mend; %color(ColorD); %color(ColorE); %color(ColorF); ods excel close; /*---BOTTOM LAYER ---*/ ods results on; ods listing;


 

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 4 replies
  • 806 views
  • 8 likes
  • 4 in conversation