topic SUM a variable with BY command over a five-year horizon in New SAS User

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

jiaxinyang — Tue, 10 Sep 2019 15:21:20 GMT

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

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

PaigeMiller — Tue, 10 Sep 2019 15:37:12 GMT

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.

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

jiaxinyang — Tue, 10 Sep 2019 15:43:27 GMT

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

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

PaigeMiller — Tue, 10 Sep 2019 15:53:38 GMT

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

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

jiaxinyang — Tue, 10 Sep 2019 15:54:51 GMT

Thanks a lot!

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

jiaxinyang — Wed, 02 Oct 2019 14:40:39 GMT

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