How do I difference year to date variables into quarterly figures?

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

How do I difference year to date variables into quarterly figures?

Hi there!

I am trying to find the quarterly expenditures for a year to date variable.  For example, let's look at capital expenditure (capxy).

Q1 capxy= 100

Q2 capxy= 300

Q3 capxy=450 and

Q4 capxy=525.

 

Since my variable is year to date, my quarterly expenditures are actually

Q1 capxy= 100

Q2 capxy= (300-100) = 200

Q3 capxy=(450-300) = 150 and

Q4 capxy=(525 - 450) = 75.

 

Now, I have panel data with many firms over many years.  My data is set up similar:

 

 

Firm     Quarter     CAPXY

1          2001.1       100

1          2001.2       300

1          2001.3       450

1          2001.4       525

1          2002.1       110

1          2002.2       210

1          2002.3       320

1          2002.4       350

1          2004.2       50     **

1          2004.3       75

1          2004.4       105

2          2001.1       25

2          2001.2       35

2          2001.3       40

3          2001.4       110       ***

3          2002.1       100

3          2002.2       175  

 

**NOTICE: For firm 1, I do not have consecutive data. On my actual dataset, not all firms have consecutive data- a few quit trading for a intermittent years.

 

***NOTICE: Must take firm number into consideration.  Simply coding it by quarter is not sufficient because it might not recognize a firm change.

 

My question now is, how do I most efficiently code my capxy year-to-date variable to actually be the difference between quarters each year? The caveat is that it has to start over each year taking the firm number and possible nonsecutive periods into account.

 

I want the above code to look like this:

 

 

Firm     Quarter     CAPXY_YTD  CAPXY

1          2001.1       100                 100

1          2001.2       300                 200

1          2001.3       450                 150

1          2001.4       525                 75

1          2002.1       110                 110

1          2002.2       210                 100

1          2002.3       320                 110

1          2002.4       350                 30

1          2004.2       50     **           50

1          2004.3       75                   25

1          2004.4       105                 30

2          2001.1       25                   25

2          2001.2       35                   10

 2          2001.3       40                  5

3          2001.4       110   **            110

3          2002.1       100                 100

3          2002.2       175                 75

 

I have thought about a do loop, but I am not familiar with these and past attempts at using it have been non-productive. 

Thank you for taking the time to help me!


Accepted Solutions
Solution
‎04-07-2016 11:46 PM
Super User
Posts: 1,102

Re: How do I difference year to date variables into quarterly figures?

[ Edited ]

Alternatively please try

 

data have;
infile cards missover;
input firm quarter$  CAPXY_YTD :$;
year=scan(quarter,1,'.');
quat=scan(quarter,2,'.');
cards;
1          2001.1       100
1          2001.2       300
1          2001.3       450
1          2001.4       525
1          2002.1       110
1          2002.2       210
1          2002.3       320
1          2002.4       350
1          2004.2       50**
1          2004.3       75
1          2004.4       105
2          2001.1       25
2          2001.2       35
2          2001.3       40
3          2001.4       110***
3          2002.1       100
3          2002.2       175  
;

proc sort data=have;
by firm year quat;
run;

data want;
set have;
by firm year quat;
 CAPXY=input(compress(CAPXY_YTD,'*'),best.)-lag(input(compress(CAPXY_YTD,'*'),best.));
if first.year then  CAPXY=input(compress(CAPXY_YTD,'*'),best.);
run;
Thanks,
Jag

View solution in original post


All Replies
Grand Advisor
Posts: 17,325

Re: How do I difference year to date variables into quarterly figures?

You need BY group processing as then you can use the first. and last. to identify a firm switch as well as a year switch.  

 

I would consider adding the missing quarters with a 0 value so you can avoid having to deal with them in the summation.

 

To sum you can use the retain to keep the value across rows.

 

SAS loops through all the rows by default so you DO NOT need a do loop. 

 

 

Grand Advisor
Posts: 9,571

Re: How do I difference year to date variables into quarterly figures?

So when QTR=1, you also want reset it ?

 

 

data have;
input Firm     Quarter $    CAPXY;
q=input(translate(Quarter,'Q','.'),yyq9.);
format q yyq.;
cards;
1          2001.1       100
1          2001.2       300
1          2001.3       450
1          2001.4       525
1          2002.1       110
1          2002.2       210
1          2002.3       320
1          2002.4       350
1          2004.2       50     
1          2004.3       75
1          2004.4       105
2          2001.1       25
2          2001.2       35
2          2001.3       40
3          2001.4       110      
3          2002.1       100
3          2002.2       175  
;
run;
data want;
 set have;
 by Firm;
 want=dif(CAPXY);
 lag_q=lag(q);
 if first.Firm or qtr(q)=1 or intck('qtr',lag_q,q) ne 1 then want=CAPXY;
run;
Solution
‎04-07-2016 11:46 PM
Super User
Posts: 1,102

Re: How do I difference year to date variables into quarterly figures?

[ Edited ]

Alternatively please try

 

data have;
infile cards missover;
input firm quarter$  CAPXY_YTD :$;
year=scan(quarter,1,'.');
quat=scan(quarter,2,'.');
cards;
1          2001.1       100
1          2001.2       300
1          2001.3       450
1          2001.4       525
1          2002.1       110
1          2002.2       210
1          2002.3       320
1          2002.4       350
1          2004.2       50**
1          2004.3       75
1          2004.4       105
2          2001.1       25
2          2001.2       35
2          2001.3       40
3          2001.4       110***
3          2002.1       100
3          2002.2       175  
;

proc sort data=have;
by firm year quat;
run;

data want;
set have;
by firm year quat;
 CAPXY=input(compress(CAPXY_YTD,'*'),best.)-lag(input(compress(CAPXY_YTD,'*'),best.));
if first.year then  CAPXY=input(compress(CAPXY_YTD,'*'),best.);
run;
Thanks,
Jag
Contributor
Posts: 23

Re: How do I difference year to date variables into quarterly figures?

Thanks everyone for helping.

 

Jag, your solution worked beautifully. Thanks again!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 307 views
  • 1 like
  • 4 in conversation