topic Re: Adding a new sub-type of the variable as the difference of two in SAS Programming

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

Astana — Mon, 07 Oct 2019 22:03:32 GMT

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.

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

Reeza — Mon, 07 Oct 2019 22:27:58 GMT

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

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

hashman — Tue, 08 Oct 2019 01:23:44 GMT

@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.

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

Ksharp — Tue, 08 Oct 2019 11:39:48 GMT

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;

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

hashman — Tue, 08 Oct 2019 18:36:55 GMT

@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.

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

Astana — Wed, 09 Oct 2019 14:59:36 GMT

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

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

Astana — Wed, 09 Oct 2019 15:01:46 GMT

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

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

hashman — Wed, 09 Oct 2019 20:40:44 GMT

@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.

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

Astana — Wed, 09 Oct 2019 22:37:01 GMT

Thank you! That is what I needed.