topic Re: How to Split a Table to Create Multiple Excel Reports in SAS Programming

How to Split a Table to Create Multiple Excel Reports

SASMom2 — Thu, 01 Aug 2019 01:15:17 GMT

I have one dataset in SAS that will be used to create a final table to export out into Excel using ODS. This table has more than 20 customers. I need to create separate excel reports for each of these customers through ODS. Below is sample data from the table that has data for all 20 plus customers.

I would like to create three reports. One for ABC, one for DEF and one for GHI. I know I can use Data step to split out the tables but I need to do this dynamically since new customers are added all the time..

Thanks in advance.

Customer	ID	Transaction ID	Transaction_Date
ABC	101	123456	1/1/2019
ABC	101	123	2/3/2019
ABC	103	3456	4/3/2019
ABC	104	23456	5/6/2019
DEF	101	345678	3/2/2019
DEF	101	4567	5/2/2019
DEF	102	5432	7/20/2019
DEF	103	445454	1/6/2019
GHI	101	789898	8/1/2019
GHI	101	6576577	5/20/2019
GHI	101	4533	4/20/2019
GHI	101	4354	6/6/2019
GHI	101	5676	5/8/2019

Re: How to Split a Table to Create Multiple Excel Reports

Tom — Thu, 01 Aug 2019 01:19:26 GMT

Just use a BY statement.

Re: How to Split a Table to Create Multiple Excel Reports

Reeza — Thu, 01 Aug 2019 01:23:17 GMT

Do you need a new workbook for each group, or a new worksheet? If it's a new worksheet this is really easy. If it's a new workbook it's harder.

Here's a tutorial on how this can be done though, which requires that you start with a working program that does it for one group.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

@SASMom2 wrote:

I have one dataset in SAS that will be used to create a final table to export out into Excel using ODS. This table has more than 20 customers. I need to create separate excel reports for each of these customers through ODS. Below is sample data from the table that has data for all 20 plus customers.

I would like to create three reports. One for ABC, one for DEF and one for GHI. I know I can use Data step to split out the tables but I need to do this dynamically since new customers are added all the time..

Thanks in advance.

Customer ID Transaction ID Transaction_Date

ABC 101 123456 1/1/2019

ABC 101 123 2/3/2019

ABC 103 3456 4/3/2019

ABC 104 23456 5/6/2019

DEF 101 345678 3/2/2019

DEF 101 4567 5/2/2019

DEF 102 5432 7/20/2019

DEF 103 445454 1/6/2019

GHI 101 789898 8/1/2019

GHI 101 6576577 5/20/2019

GHI 101 4533 4/20/2019

GHI 101 4354 6/6/2019

GHI 101 5676 5/8/2019

Re: How to Split a Table to Create Multiple Excel Reports

SASMom2 — Thu, 01 Aug 2019 01:51:49 GMT

I need to create a separate excel report for each customer. So, for the sample data, I will have three different excel files.

Re: How to Split a Table to Create Multiple Excel Reports

SASMom2 — Thu, 01 Aug 2019 01:56:18 GMT

can you please give me an example of the 'by' statement?

Thanks

Re: How to Split a Table to Create Multiple Excel Reports

Reeza — Thu, 01 Aug 2019 02:15:00 GMT

Unfortunately if you want a new file you need a macro or dynamic approach, BY group processing will not work, AFAIK. You can do that for other destinations, such as PDF but then you still don't have control over the file name, so you'll likely end up either writing a macro or using DOSUBL/CALL EXECUTE(). If you need to write and maintain this code, I will recommend the same solution I previously posted. At the same time, I'm very happy to be proven wrong, if there's an easier way to do this, it's always great to know 🙂

Does your excel file need any formatting as well, or just a raw data dump?

@SASMom2 wrote:

I need to create a separate excel report for each customer. So, for the sample data, I will have three different excel files.

Re: How to Split a Table to Create Multiple Excel Reports

SASMom2 — Thu, 01 Aug 2019 02:33:29 GMT

We do format all our reports but it is not that important...

Re: How to Split a Table to Create Multiple Excel Reports

hashman — Thu, 01 Aug 2019 03:51:46 GMT

@SASMom2:

You need to split the file dynamically and write it out to separate excel files dynamically, too. One approach of doing this may look like:

data have ;                                                                           
  input Customer $ ID Transaction_ID Transaction_Date :mmddyy10. ;                    
  format transaction_date mmddyy10. ;                                                 
  cards ;                                                                             
ABC  101   123456    1/1/2019                                                         
ABC  101      123    2/3/2019                                                         
ABC  103     3456    4/3/2019                                                         
ABC  104    23456    5/6/2019                                                         
DEF  101   345678    3/2/2019                                                         
DEF  101     4567    5/2/2019                                                         
DEF  102     5432    7/20/2019                                                        
DEF  103   445454    1/6/2019                                                         
GHI  101   789898    8/1/2019                                                         
GHI  101  6576577    5/20/2019                                                        
GHI  101     4533    4/20/2019                                                        
GHI  101     4354    6/6/2019                                                         
GHI  101     5676    5/8/2019                                                         
run ;                                                                                 
                                                                                      
%let r = c:\temp ;                                                                    
data _null_ ;                                                                         
  if _n_ = 1 then do ;                                                                
    dcl hash h () ;                                                                   
    h.definekey ("_n_") ;                                                               
    h.definedata ("Customer", "ID", "Transaction_ID", "Transaction_Date") ;           
    h.definedone () ;                                                                 
  end ;                                                                               
  h.clear() ;                                                                        
  do _n_ = 1 by 1 until (last.customer) ;                                               
    set have ;                                                                        
    by customer ;                                                                     
    h.add() ;                                                                         
  end ;                                                                               
  h.output (dataset: catx ("_", "x", customer)) ;                                     
  call execute (cats ("ods excel file='&r\", customer, ".xlsx';")) ;                  
  call execute (cats ("proc print noobs data=", catx ("_", "x", customer), ";run;")) ;
  call execute ("ods excel close;") ;                                                 
run ;

There're 3 assumptions:

Your customer values don't contain characters disallowed in SAS data set names
They contain no characters disallowed in the file path names of the operating system you're using
The path used for R (i.e. the root) exists in your system and you're allowed to write there; change as needed

On my Windows laptop (where the path c:\temp exists) the code above works perfectly fine. You may want to turn your HTML and listing ODS destinations off before running this and turn them back on when it's finished.

Kind regards

Paul D.

Re: How to Split a Table to Create Multiple Excel Reports

andreas_lds — Thu, 01 Aug 2019 05:13:08 GMT

Impressive @hashman

Because i am bone-idle, i would use h.defineData(all: 'yes'), to avoid listing all vars.

Re: How to Split a Table to Create Multiple Excel Reports

SASMom2 — Thu, 01 Aug 2019 06:09:54 GMT

Thanks Paul. It worked perfectly! I had to use ' goptions device=png;' statement as I was getting some errors.

Not to be too greedy but do you think I can use couple of other tables to create two more tabs in each of these files?

I can't believe that it worked without changing much. I am not very advanced SAS programmer so I will have to understand how it works but for now, I am super excited!

Thanks

Re: How to Split a Table to Create Multiple Excel Reports

SASMom2 — Thu, 01 Aug 2019 06:11:14 GMT

Good idea. I will try that.

Re: How to Split a Table to Create Multiple Excel Reports

hashman — Thu, 01 Aug 2019 06:15:37 GMT

@andreas_lds:

Thanks. Using ALL:"Y" is possible, but then your bone-idleness may be still disturbed by the need to alter the code quite a bit to accommodate it . This is because it will work only if:

you specify the DATASET argument tag to the constructor (with OBS=0 to avoid unneeded hash table loading)
also MULTIDATA:"Y" because now you can't use _N_ as a key (it's not on the input file)
specify CUSTOMER (or any other field from HAVE) as the hash key instead

In other words, you'd have to code something like:

...
data _null_ ;                                          
  if _n_ = 1 then do ;                                 
    dcl hash h (dataset:"have(obs=0)", multidata:"y") ;
    h.definekey ("customer") ;                         
    h.definedata (all:"y") ;                           
    h.definedone () ;                                  
  end ;                                                
  h.clear () ;                                         
  do /*_n_ = 1 by 1*/ until (last.customer) ;          
    set have ;                                         
    by customer ;                                      
    h.add() ;                                          
  end ;    
...

I agree that listing all the input data set variables as hash data variables is off-putting since it's hard coding. However, besides using ALL:"Y" argument tag and the associated subterfuges notes above, that can be also dealt with by using the compiler symbol table to call DEFINEDATA to add one variable at a time:

...
  if _n_ = 1 then do ;                                                            
    dcl hash h () ;                                                               
    h.definekey ("_n_") ;                                                         
    if 0 then set have ;                                                          
    length _v $ 32 ;                                                              
    do until (cmiss (_v)) ;                                                       
      call vnext (_v) ;                                                           
      if lowcase (_v) notin: ("_", "first.", "last.", "") then h.definedata (_v) ;
    end ;                                                                         
    h.definedone () ;                                                             
  end ;                                                                           
  h.clear () ;                                                                    
  do _n_ = 1 by 1 until (last.customer) ;                                         
    set have ;                                                                    
    by customer ;                                                                 
    h.add() ;                                                                     
  end ;                                                                           
...

Kind regards

Paul D.

Re: How to Split a Table to Create Multiple Excel Reports

hashman — Thu, 01 Aug 2019 06:26:08 GMT

@SASMom2:

It's possible. But you'll have to encode the appropriate ODS EXCEL options - most likely, SHEET_INTERVAL='TABLE' - into CALL EXECUTE.

The main thing is, first think of the SAS code you want to generate. Then think of how to write CALL EXECUTE (or any other code generation method) to generate it.

Best regards

Paul D.

Re: How to Split a Table to Create Multiple Excel Reports

SASMom2 — Thu, 01 Aug 2019 13:41:39 GMT

I am not very familiar with the hash objects so I need more help adding two more tables. So, I have tables A, B and C with common fields Customer and ID. How should I add tables B and C in your original code?

Thanks