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 is hosting free webinars!
Next up: Troy Martin Hughes presents Calling Open-Source Python Functions within SAS PROC FCMP: A Google Maps API Geocoding Adventure on Wednesday April 23.
Register now 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-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1323 views
  • 8 likes
  • 4 in conversation