topic Re: SAS - calculate average values of previous n quarters in New SAS User

SAS - calculate average values of previous n quarters

adrfinance — Thu, 21 Nov 2019 12:05:35 GMT

I have a SAS dataset that looks like:

             Obs    qy         Time       beginning   end                   

               1    2008 Q1    2008Q1      1           1                    
               2    2008 Q1    2008Q1      1           2                    
               3    2008 Q2    2008Q2      1           1                    
               4    2008 Q2    2008Q2      1           2                    
               5    2008 Q2    2008Q2      1           1                    
               6    2008 Q3    2008Q3      1           1                    
               7    2008 Q4    2008Q4      1           1                    
               8    2008 Q4    2008Q4      1           2                    
               9    2008 Q4    2008Q4      1           1                    
              10    2009 Q1    2009Q1      1           1

and I would like to have some new variables containing the accounts that had beginning=1 and end=2 per quarter, for the n previous quarters (n could be 1,2,3,4 etc). How can I do that?

Desired output:

             Obs    qy         Time       beginning   end      one_quarter_back             

               1    2008 Q1    2008Q1      1           1   0.25                  
               2    2008 Q1    2008Q1      1           2   0.25                 
               3    2008 Q2    2008Q2      1           1   0.5                 
               4    2008 Q2    2008Q2      1           2   0.5              
               5    2008 Q2    2008Q2      1           1   0.5              
               6    2008 Q3    2008Q3      1           1   0.33              
               7    2008 Q4    2008Q4      1           1   0.4             
               8    2008 Q4    2008Q4      1           2   0.4              
               9    2008 Q4    2008Q4      1           1   0.4                 
              10    2009 Q1    2009Q1      1           1   0.15

(The above assuming that the respective calculations led to these numbers, and we would have similar for 2,3,4 etc quarters back)

Re: SAS - calculate average values of previous n quarters

PaigeMiller — Thu, 21 Nov 2019 12:23:22 GMT

@adrfinance wrote:

I have a SAS dataset that looks like:

             Obs    qy         Time       beginning   end                   

               1    2008 Q1    2008Q1      1           1                    
               2    2008 Q1    2008Q1      1           2                    
               3    2008 Q2    2008Q2      1           1                    
               4    2008 Q2    2008Q2      1           2                    
               5    2008 Q2    2008Q2      1           1                    
               6    2008 Q3    2008Q3      1           1                    
               7    2008 Q4    2008Q4      1           1                    
               8    2008 Q4    2008Q4      1           2                    
               9    2008 Q4    2008Q4      1           1                    
              10    2009 Q1    2009Q1      1           1

and I would like to have some new variables containing the accounts that had beginning=1 and end=2 per quarter, for the n previous quarters (n could be 1,2,3,4 etc). How can I do that?

"Accounts"? I'm not sure what that word is referring to. There is no obvious "account" variable.

Can you further explain the part about "had beginning=1 and end=2 per quarter"? Because for 2008Q3, this condition is not met, yet 2008Q3 winds up on the output data set.

Desired output:

             Obs    qy         Time       beginning   end      one_quarter_back             

               1    2008 Q1    2008Q1      1           1   0.25                  
               2    2008 Q1    2008Q1      1           2   0.25                 
               3    2008 Q2    2008Q2      1           1   0.5                 
               4    2008 Q2    2008Q2      1           2   0.5              
               5    2008 Q2    2008Q2      1           1   0.5              
               6    2008 Q3    2008Q3      1           1   0.33              
               7    2008 Q4    2008Q4      1           1   0.4             
               8    2008 Q4    2008Q4      1           2   0.4              
               9    2008 Q4    2008Q4      1           1   0.4                 
              10    2009 Q1    2009Q1      1           1   0.15

(The above assuming that the respective calculations led to these numbers, and we would have similar for 2,3,4 etc quarters back)

Is beginning always equal to 1? Can you provide a more "realistic" example, where beginning is not always equal to 1? Or at least provide data that matches the explanation so that "accounts" are clear? Also, please explain the method of calculation that results in a value of 0.25 in the first row under ONE_QUARTER_BACK.

Thanks.

Re: SAS - calculate average values of previous n quarters

adrfinance — Thu, 21 Nov 2019 12:25:06 GMT

Account = one row of data in this case...

the beginning is always equal to 1.

I want to count the percentage of accounts that have beginning equal to 1 and end equal to 2, per quarter.

             Obs    qy         Time       beginning   end      one_quarter_back             

               1    2008 Q1    2008Q1      1           1   0.5                  
               2    2008 Q1    2008Q1      1           2   0.5                 
               3    2008 Q2    2008Q2      1           1   0.33                 
               4    2008 Q2    2008Q2      1           2   0.33             
               5    2008 Q2    2008Q2      1           1   0.33              
               6    2008 Q3    2008Q3      1           1   0              
               7    2008 Q4    2008Q4      1           1   0.33             
               8    2008 Q4    2008Q4      1           2   0.33              
               9    2008 Q4    2008Q4      1           1   0                 
              10    2009 Q1    2009Q1      1           1   0

This would be the correct table if these were my only data

Re: SAS - calculate average values of previous n quarters

PaigeMiller — Thu, 21 Nov 2019 12:30:26 GMT

Thank you, that's much clearer now.

data have2;
    set have;
    /* Since beginning is always equal to 1, I don't use it here in my code */
    if /* beginning=1 and */ end=2 then flag=1;
    else flag=0;
run;
proc summary nway data=have2;
    class qy;
    var flag;
    output out=want mean=one_quarter_back;
run;

If you want, you can merge output data set WANT back in with the original data set HAVE.

Re: SAS - calculate average values of previous n quarters

adrfinance — Thu, 21 Nov 2019 12:38:08 GMT

Thank you Paige! How can I merge it with my original dataset? And also how can I get it for the previous quarters as well?

Re: SAS - calculate average values of previous n quarters

PaigeMiller — Thu, 21 Nov 2019 12:48:52 GMT

You do a DATA step MERGE

I'm still not sure what you mean by "how can I get it for the previous quarters as well?" Can you give an example?

Re: SAS - calculate average values of previous n quarters

adrfinance — Thu, 21 Nov 2019 12:52:01 GMT

             Obs    qy         Time       beginning   end      one_quarter_back    two_quarters_back         

               1    2008 Q1    2008Q1      1           1   0.5                     .
               2    2008 Q1    2008Q1      1           2   0.5                     .
               3    2008 Q2    2008Q2      1           1   0.33                    0.5
               4    2008 Q2    2008Q2      1           2   0.33                    0.5
               5    2008 Q2    2008Q2      1           1   0.33                    0.5
               6    2008 Q3    2008Q3      1           1   0                       0.33
               7    2008 Q4    2008Q4      1           1   0.33                    0
               8    2008 Q4    2008Q4      1           2   0.33                    0
               9    2008 Q4    2008Q4      1           1   0                       0.33
              10    2009 Q1    2009Q1      1           1   0                       0.33

this is what two_quarters_back would look like. How do I do a DATA step MERGE?

Re: SAS - calculate average values of previous n quarters

Ksharp — Thu, 21 Nov 2019 12:53:22 GMT

data have;
input Obs    qy   :yyq.     Time    :yyq.     beginning   end   ;  
format qy yyq.; 
drop time;
cards;
               1    2008Q1    2008Q1      1           1                    
               2    2008Q1    2008Q1      1           2                    
               3    2008Q2    2008Q2      1           1                    
               4    2008Q2    2008Q2      1           2                    
               5    2008Q2    2008Q2      1           1                    
               6    2008Q3    2008Q3      1           1                    
               7    2008Q4    2008Q4      1           1                    
               8    2008Q4    2008Q4      1           2                    
               9    2008Q4    2008Q4      1           1                    
              10    2009Q1    2009Q1      1           1   
;
proc sql;
create table want as
select *,

(select sum(beginning=1 and end=2) from have 
where qy between intnx('qtr',a.qy,-1) and a.qy)/
(select count(*) from have 
where qy between intnx('qtr',a.qy,-1) and a.qy) as one_quarter_back  ,


(select sum(beginning=1 and end=2) from have 
where qy between intnx('qtr',a.qy,-2) and a.qy)/
(select count(*) from have 
where qy between intnx('qtr',a.qy,-2) and a.qy) as two_quarter_back  ,


(select sum(beginning=1 and end=2) from have 
where qy between intnx('qtr',a.qy,-3) and a.qy)/
(select count(*) from have 
where qy between intnx('qtr',a.qy,-3) and a.qy) as three_quarter_back  

 from have as a;
quit;

Re: SAS - calculate average values of previous n quarters

adrfinance — Thu, 21 Nov 2019 13:00:41 GMT

I get the following error:

ERROR: Function INTNX requires a numeric expression as argument 2.

probably qy is not numeric?

Re: SAS - calculate average values of previous n quarters

PaigeMiller — Thu, 21 Nov 2019 13:06:56 GMT

DATA step merge

data merged;
     merge have want;
     by qy;
run;

But don't do this if you want the additional quarters.

Instead, to get the additional quarters into the results, do this:

data have2;
    set have;
    /* Since beginning is always equal to 1, I don't use it here in my code */
    if /* beginning=1 and */ end=2 then flag=1;
    else flag=0;
run;
proc summary nway data=have2;
    class qy;
    var flag;
    output out=want mean=one_quarter_back;
run;
data want2;
     set want;
     two_quarter_back=lag(one_quarter_back);
     three_quarter_back=lag2(one_quarter_back);
     four_quarter_back=lag3(one_quarter_back);
run;
data merged;
    merge have want2;
    by qy;
run;

Re: SAS - calculate average values of previous n quarters

adrfinance — Thu, 21 Nov 2019 13:13:40 GMT

Paige, In the last step you use want2 and want but you haven't defined these, right?