SUM a variable with BY command over a five-year horizon

[jiaxinyang]

Hello everyone,

I am new to SAS (using SAS 9.4) and I need some help with one variable I use for my research.

I need to generate a variable (let's call it variable A for simplicity), which is the sum of variable B over a five-year horizon, from the current year back to four years ago (i.e. from year t-4 to year t). I also needs to use BY Group command for variable A to make sure the sum is with respect to each firm. On top of this, variable B needs to have at least three consecutive years of non-missing data for computing variable A.

I looked at the SUM function and I couldn't figure out how to do the sum for an interval with BY command, and also considering non-missing value.

I really appreciate any help. Thanks a lot in advance.

Best regards,

Jiaxin

[PaigeMiller]

You'll have to do the testing to see if there are 3 years without missings separately, but PROC SUMMARY will do sums of BY groups over the desired time period.

[jiaxinyang]

Hi Paige,

Thanks for your prompt reply. I will look at PROC SUMMARY first.

Is there a command I can use to check the missing values for each firm? I need BY groups to see if there are 3 years without missing values as well.

Thanks again for your help.

Best regards,

Jiaxin

[PaigeMiller · Accepted solution]

---

@jiaxinyang wrote:

Is there a command I can use to check the missing values for each firm? I need BY groups to see if there are 3 years without missing values as well.

---

You have to program this yourself. PROC SUMMARY can count the number of missings over a three year period for each BY group, using the NMISS= output option. Then you know which BY groups don't meet the criterion and you can exclude them from the SUM, which would happen in the next PROC SUMMARY.

[jiaxinyang]

Thanks a lot!

[jiaxinyang]

Hi Paige Miller,

I wasn't able to use NMISS as I cannot find any similar examples. 

Here are what I have in my dataset. The year identifier -- fyear, the company identifier -- gvkey and the variable interested -- txt. I want the sum of txt over a five year period (year t to year t-4) and require at least three consecutive years of non-missing data.

Could you please let me know what codes I need to use or give me some examples I can refer to?

Thanks a lot.

Jiaxin