topic Re: programming for aggregating individual level data to the aggregated one in SAS Programming

programming for aggregating individual level data to the aggregated one

asinusdk — Wed, 21 Nov 2018 17:22:59 GMT

year	month	date	id	v1	v2
2001	1	1	1	1	0
2001	1	2	2	0	1
2001	1	12	2	0	1
2001	2	13	2	1	1
2002	2	14	3	1	0
2002	3	1	3	0	1
2002	5	12	3	1	1
2003	2	13	2	0	1
2003	4	14	2	0	1
2003	5	1	2	1	1
2003	5	12	3	1	0
2003	7	13	3	0	1
2003	8	14	3	0	1
2003	9	1	3	1	1
2003	10	1	3	1	0
2003	11	13	3	0	1
2003	12	25	3	0	1

From this data, I want to make a different table by summing frequencies from each variable by year and moth.

The expected result will be like this.

year	month	v1_freq	v2_freq
2001	1	1	2
2001	2	2	1
2001	3	0	0
2001	4	0	0
2001	5	0	0
2001	6	0	0
2001	7	0	0
2001	8	0	0
2001	9	0	0
2001	10	0	0
2001	11	0	0
2001	12	0	0
2002	1	0	0
2002	2	1	0
2002	3	0	1
2002	4	0	0
2002	5	1	1
2002	6	0	0
2002	7	0	0
2002	8	0	0
2002	9	0	0
2002	10	0	0
2002	11	0	0
2002	12	0	0
2003	1	0	0
2003	2	0	1
2003	3	0	0
2003	4	0	1
2003	5	2	1
2003	6	0	0
2003	7	0	1
2003	8	0	1
2003	9	1	1
2003	10	1	0
2003	11	0	1
2003	12	0	1

What codes can I use?

Thank you so much in advance!

Re: programming for aggregating individual level data to the aggregated one

PaigeMiller — Wed, 21 Nov 2018 17:33:10 GMT

proc summary data=have nway;
    class year month;
    var v1 v2;
    output out=want sum=v1freq v2freq;
run;

The rows for months that have no data will not appear in the output via this method. If you need them to appear, you would have to augment your original data set so that there is at least one record for each year/month combination.

Re: programming for aggregating individual level data to the aggregated one

Reeza — Wed, 21 Nov 2018 17:31:24 GMT

Use PROC MEANS to summarize your data. You control the levels of aggregating using the CLASS statement.

If you need more than one level, ie totals or such then you want to look into WAYS and TYPES but otherwise this will get you there:

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

@asinusdk wrote:

year month date id v1 v2

2001 1 1 1 1 0

2001 1 2 2 0 1

2001 1 12 2 0 1

2001 2 13 2 1 1

2002 2 14 3 1 0

2002 3 1 3 0 1

2002 5 12 3 1 1

2003 2 13 2 0 1

2003 4 14 2 0 1

2003 5 1 2 1 1

2003 5 12 3 1 0

2003 7 13 3 0 1

2003 8 14 3 0 1

2003 9 1 3 1 1

2003 10 1 3 1 0

2003 11 13 3 0 1

2003 12 25 3 0 1

From this data, I want to make a different table by summing frequencies from each variable by year and moth.

The expected result will be like this.

year month v1_freq v2_freq

2001 1 1 2

2001 2 2 1

2001 3 0 0

2001 4 0 0

2001 5 0 0

2001 6 0 0

2001 7 0 0

2001 8 0 0

2001 9 0 0

2001 10 0 0

2001 11 0 0

2001 12 0 0

2002 1 0 0

2002 2 1 0

2002 3 0 1

2002 4 0 0

2002 5 1 1

2002 6 0 0

2002 7 0 0

2002 8 0 0

2002 9 0 0

2002 10 0 0

2002 11 0 0

2002 12 0 0

2003 1 0 0

2003 2 0 1

2003 3 0 0

2003 4 0 1

2003 5 2 1

2003 6 0 0

2003 7 0 1

2003 8 0 1

2003 9 1 1

2003 10 1 0

2003 11 0 1

2003 12 0
1

What codes can I use?

Thank you so much in advance!

Re: programming for aggregating individual level data to the aggregated one

hashman — Thu, 22 Nov 2018 03:46:46 GMT

@asinusdk:

Assuming that your input data are sorted by YEAR, MONTH, you can use a single DATA step to:

aggregate within each (YEAR,MONTH) by-group
after the last by-group record is processed:
look ahead into the first record of the next by-group
use the date from that next record to generate the necessary records to fill the temporal gap

In the SAS language:

data have ;                                                                
  input year month v1 v2 ;                                                 
  cards ;                                                                  
2001  1  1  0                                                              
2001  1  0  1                                                              
2001  1  0  1                                                              
2001  2  1  1                                                              
2002  2  1  0                                                              
2002  3  0  1                                                              
2002  5  1  1                                                              
2003  2  0  1                                                              
2003  4  0  1                                                              
2003  5  1  1                                                              
2003  5  1  0                                                              
2003  7  0  1                                                              
2003  8  0  1                                                              
2003  9  1  1                                                              
2003 10  1  0                                                              
2003 11  0  1                                                              
2003 12  0  1                                                              
run ;                                                                      
                                                                           
data want (drop = _:) ;                                                    
  set have (keep = year month v:) ;                                        
  by year month ;                                                          
  v1_freq + v1 ;                                                           
  v2_freq + v2 ;                                                           
  if last.month ;                                                          
  output ;                                                                 
  v1_freq = 0 ;                                                            
  v2_freq = 0 ;                                                            
  if _n_ < n ;                                                             
  p = _n_ + 1 ;                                                            
  set have (keep = year month rename = (year=_y month=_m)) point=p nobs=n ;
  _dt = mdy (month, 1, year) ;                                             
  do _q = 2 to intck ("mon", _dt, mdy (_m, 1, _y)) ;                       
    _dt = intnx ("mon", _dt, 1) ;                                          
    year = year (_dt) ;                                                    
    month = month (_dt) ;                                                  
    output ;                                                               
  end ;                                                                    
run ;

If the input data set isn't sorted, basically the same gap-filling scheme can be enacted using the hash object by:

making it aggregate the sums
augmenting the hash table with the dates not found in the table and coupled with zero sums

In SAS words:

data _null_ ;                                                           
  dcl hash h (ordered:"a") ;                                            
  h.definekey ("year", "month") ;                                       
  h.definedata ("year", "month", "v1_freq", "v2_freq") ;                
  h.definedone () ;                                                     
  do until (z) ;                                                        
    set have (keep = year month v:) end = z ;                           
    if h.find() ne 0 then call missing (v1_freq, v2_freq) ;             
    v1_freq + v1 ;                                                      
    v2_freq + v2 ;                                                      
    h.replace() ;                                                       
    _ldt = min (_ldt, mdy (month, 1, year)) ;                           
    _hdt = max (_hdt, mdy (month, 1, year)) ;                           
  end ;                                                                 
  do _n_ = 1 to intck ("mon", _ldt, _hdt) ;                             
    _ldt = intnx ("mon", _ldt, 1) ;                                     
    year = year (_ldt) ;                                                
    month = month (_ldt) ;                                              
    h.ref (key:year, key:month, data:year, data:month, data:0, data:0) ;
  end ;                                                                 
  h.output (dataset:"want") ;                                          
run ;

Of course, the problem can be solved using either MEANS/SUMMARY, as suggested earlier, or SQL, with the aggregated result set left-joined with a separately prepared data set containing the fill-gap (YEAR,MONTH) and zero sums. Preparing such a data set is principally no different from running a DO loop similar to the DO _N_= loop above. The advantage of the hash object is that the entire processing can be done in a single step and a single pass through the input data, regardless of the input key sequence.

Paul D.

Re: programming for aggregating individual level data to the aggregated one

singhsahab — Thu, 22 Nov 2018 07:22:55 GMT

Data have ;                                                                
  input year month v1 v2 ;                                                 
  cards ;                                                                  
2001  1  1  0                                                              
2001  1  0  1                                                              
2001  1  0  1                                                              
2001  2  1  1                                                              
2002  2  1  0                                                              
2002  3  0  1                                                              
2002  5  1  1                                                              
2003  2  0  1                                                              
2003  4  0  1                                                              
2003  5  1  1                                                              
2003  5  1  0                                                              
2003  7  0  1                                                              
2003  8  0  1                                                              
2003  9  1  1                                                              
2003 10  1  0                                                              
2003 11  0  1                                                              
2003 12  0  1  
;                                                            
run ;                                                                      
      
PROC SQL;
CREATE TABLE WANT AS
SELECT YEAR,MONTH,SUM(V1) AS FR_V1,SUM(V2) AS FR_V2 FROM HAVE GROUP BY YEAR ,MONTH;
QUIT;

If you wanted to use PROC SQL above code can help. if wanted to see all month data at least one entry is needed for each month per year.

Thanks...