topic Re: Multiple dynamic files from one file based on employee id in SAS Programming

Multiple dynamic files from one file based on employee id

Babloo — Mon, 24 Jun 2019 17:02:23 GMT

I have to create multiple dynamic files based one single file which has employee id, reporting date and other variables. If the file has 10 different employee ids and reporting date then it should create 10 files.

As is :

Emp_ID reporting_dt idv
100000 17mar2019 31
100001 21may2018 36
100000 17mar2019 32

To be:

File1 should have

Emp_id reporting_dt idv
100000 17mar2019 31
100000 17mar2019 32

File 2 should have

Emp_id reporting_dt idv
100001 21may2018 36

Re: Multiple dynamic files from one file based on employee id

Kurt_Bremser — Mon, 24 Jun 2019 17:10:45 GMT

What for? You can always use where conditions to use only a needed subset, or use by-group processing to do all analysis in one step.

Re: Multiple dynamic files from one file based on employee id

Reeza — Mon, 24 Jun 2019 17:16:16 GMT

If the report is to Excel or PDF and has specific formatting, consider this approach:

1. Create a program that does it for one person/employee

2. Make it generic for all other employee's by turning it into a macro.

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

If this is to a text file (csv, txt, dat) with limited formatting, then you can do that in a single data step. The documentation on the FILENAME statement has an example of how to dynamically write to different output files. I'll assume you can navigate the documentation yourself.

@Babloo wrote:
I have to create multiple dynamic files based one single file which has employee id, reporting date and other variables. If the file has 10 different employee ids and reporting date then it should create 10 files.

As is :

Emp_ID reporting_dt idv
100000 17mar2019 31
100001 21may2018 36
100000 17mar2019 32

To be:

File1 should have

Emp_id reporting_dt idv
100000 17mar2019 31
100000 17mar2019 32

File 2 should have

Emp_id reporting_dt idv
100001 21may2018 36

Re: Multiple dynamic files from one file based on employee id

hashman — Mon, 24 Jun 2019 18:56:16 GMT

@Babloo :

I second @Kurt_Bremser 's "what for?" notion. But if you really need it, there're many ways. One is to index the file by EMP_ID to enable BY processing (instead of sorting the whole thing) and then use a hash to split it dynamically:

data have ;                                       
  input Emp_ID rpt_dt:date. idv ;                 
  cards ;                                         
100003 31dec2018 33                               
100001 21may2018 31                               
100003 31dec2018 33                               
100002 01jun2018 32                               
100003 11aug2018 33                               
100002 15dec2018 32                               
run ;                                             
                                                  
proc sql ;                                        
  create index emp_id on have (emp_id) ;          
quit ;                                            
                                                  
data _null_ ;                                     
  if _n_ = 1 then do ;                            
    dcl hash h() ;                                
    h.definekey ("_n_") ;                         
    h.definedata ("emp_id", "rpt_dt", "idv") ;    
    h.definedone () ;                             
  end ;                                           
  do _n_ = 1 by 1 until (last.emp_id) ;           
    set have ;                                    
    by emp_id ;                                   
    h.add() ;                                     
  end ;                                           
  h.output (dataset: catx ("_", "have", emp_id)) ;
  h.clear() ;                                     
run ;

A more traditional, 2-pass, approach is (1) create a list of unique EMP_ID values on the first pass and (2) generate code with the case structure (or an IF-THEN-ELSE series). This, too, can be done in an infinite number of ways. Here's one:

data _null_ ;                                                                                
  dcl hash h (dataset:"have", ordered:"A") ;                                                 
  h.definekey ("emp_id") ;                                                                   
  h.definedone() ;                                                                           
  dcl hiter hi ("h") ;                                                                       
  call execute ("data ") ;                                                                   
  do while (hi.next() = 0) ;                                                                 
    call execute (catx ("_", "have", emp_id)) ;                                              
  end ;                                                                                      
  call execute ("; set have ; select (emp_id) ;") ;                                          
  do while (hi.next() = 0) ;                                                                 
    call execute (catx (" ", "when (", emp_id, ") output", catx ("_", "have", emp_id),";")) ;
  end ;                                                                                      
  call execute ("otherwise ; end ; run ;") ;                                                 
  stop ;                                                                                     
  set have ;                                                                                 
run ;

Or, if you prefer SQL to the hash object:

proc sql noprint ;                                                                                                              
  select distinct catx ("_", "have", emp_id) into :filelist separated by " " from have ;                                        
  select distinct catx (" ", "when (", emp_id, ") output", catx ("_", "have", emp_id)) into :select separated by ";" from have ;
quit ;                                                                                                                          
                                                                                                                                
data &filelist ;                                                                                                                
  set have ;                                                                                                                    
  select (emp_id) ;                                                                                                             
   &select ;                                                                                                                    
   otherwise ;                                                                                                                  
  end ;                                                                                                                         
run ;

Kind regards

Paul D.

Re: Multiple dynamic files from one file based on employee id

Babloo — Tue, 25 Jun 2019 11:49:59 GMT

I'm unable to locate one example to match my requirement.

It is the business requirement.

Re: Multiple dynamic files from one file based on employee id

Kurt_Bremser — Tue, 25 Jun 2019 12:14:08 GMT

See this example (I posted almost exactly the same code less than two weeks ago, IIRC)

data have;
input emp_id $ reporting_dt :date9. idv;
format reporting_dt yymmddd10.;
datalines;
100000 17mar2019 31
100001 21may2018 36
100000 17mar2019 32
;

proc sort
  data=have (keep=emp_id)
  out=control
  nodupkey
;
by emp_id;
run;

data _null_;
call execute('data');
do until (eof1);
  set control end=eof1;
  call execute(cats(' EMP_',emp_id));
end;
call execute('; set have; select(emp_id);');
do until (eof2);
  set control end=eof2;
  call execute('when ("' !! strip(emp_id) !! '") output EMP_' !! strip(emp_id) !! ';');
end;
call execute('end; run;');
stop;
run;