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)
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;
@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.
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
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.
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?
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?
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?
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;
Paige, In the last step you use want2 and want but you haven't defined these, right?
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;
I get the following error:
ERROR: Function INTNX requires a numeric expression as argument 2.
probably qy is not numeric?
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.