Adding a new sub-type of the variable as the difference of two

[Astana]

Hello, SAS experts.

I have following data.

date	group	value
19910731	1	23
19920731	1	43
19930731	1	54
19910731	2	34
19920731	2	23
19930731	2	43
19910731	3	23
19920731	3	34
19930731	3	23

I would like to add to this dataset group 4 with the values equals to the difference of values of group=1 and group=2 at the same date.

date	group	value
19910731	1	23
19920731	1	43
19930731	1	54
19910731	2	34
19920731	2	23
19930731	2	43
19910731	3	23
19920731	3	34
19930731	3	23
19910731	4	11
19920731	4	-23
19930731	4	-11

As the final output I would like to have a table with dates as rows and groups as columns. But the group 4 I would like to rename as "difference":

	Group 1	Group 2	Group 3	Difference
19910731	23	34	23	11
19920731	43	23	34	-23
19930731	54	43	23	-11

Could somebody help me with code.

[Reeza]

Transpose first to wide format (via PROC TRANSPOSE) and then the difference will be easier to calculate.

[hashman]

@Astana:

Not sure what the value -23 for the difference is doing in your suggested output, as 23 - 43 = -20.

At any rate, assuming this is a typo, methinks the following should do it in one fell swoop and in the requisite output file format:

data have ;                                        
  input date group value ;                         
  cards ;                                          
19910731 1 23                                      
19920731 1 43                                      
19930731 1 54                                      
19910731 2 34                                      
19920731 2 23                                      
19930731 2 43                                      
19910731 3 23                                      
19920731 3 34                                      
19930731 3 23                                      
run ;                                              
                                                   
data want (drop=group) ;                           
  merge have (where=(group=1) rename=value=group1) 
        have (where=(group=2) rename=value=group2) 
        have (where=(group=3) rename=value=group3) 
  ;                                                
  by date ;                                        
  difference = group2 - group1 ;                   
run ;                                              

Note: BY DATE is included just in case you may have other (identically structured) records with different date values.

Kind regards

Paul D. 

[Astana]

Thank you for you answer.

But this advice misses one step. First I want to get the same data, just with one more group type added (group 4).

date	group	value
19910731	1	23
19920731	1	43
19930731	1	54
19910731	2	34
19920731	2	23
19930731	2	43
19910731	3	23
19920731	3	34
19930731	3	23
19910731	4	11
19920731	4	-20
19930731	4	-11

[Ksharp]

As Reeza said.

data have ;                                        
  input date group value ;                         
  cards ;                                          
19910731 1 23                                      
19920731 1 43                                      
19930731 1 54                                      
19910731 2 34                                      
19920731 2 23                                      
19930731 2 43                                      
19910731 3 23                                      
19920731 3 34                                      
19930731 3 23                                      
run ;            
proc sort data=have out=temp;
by date;
run;
proc transpose data=temp out=want prefix=group;
by date;
id group;
var value;
run; 

[hashman]

@Ksharp:

Nice, as it internally automates the creation of group1, group2, etc.

But you need another step to compute group2-group1=difference. 

Kind regards

Paul D.

[Astana]

Thank you for you answer.

But this advice misses one step. First I want to get the same data, just with one more group type added (group 4).

date	group	value
19910731	1	23
19920731	1	43
19930731	1	54
19910731	2	34
19920731	2	23
19930731	2	43
19910731	3	23
19920731	3	34
19930731	3	23
19910731	4	11
19920731	4	-20
19930731	4	-11

[hashman · Accepted solution]

@Astana:

If this is what you want, then try:

data have ;                            
  input date group value ;             
  cards ;                              
19910731 1 23                          
19920731 1 43                          
19930731 1 54                          
19910731 2 34                          
19920731 2 23                          
19930731 2 43                          
19910731 3 23                          
19920731 3 34                          
19930731 3 23                          
run ;                                  
                                       
data step1 ;                           
  do _n_ = 1 by 1 until (last.group) ; 
    set have ;                         
    by group ;                         
    output ;                           
    array v [3,3] _temporary_ ;        
    v[group,_n_] = value ;             
  end ;                                
  if group = 3 then do _n_ = 1 to _n_ ;
    set have (keep = date) ;           
    group = 4 ;                        
    value = v[2,_n_] - v[1,_n_] ;      
    output ;                           
  end ;                                
run ;                                  

Kind regards

Paul D. 

[Astana]

Thank you! That is what I needed.