How to combine 2 excel book into one-can't use proc import due to one file has color coded

[purpleclothlady]

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

[ballardw]

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

[Quentin]

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.

[SASKiwi]

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 · Accepted solution]

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;