BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jiaxinyang
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
jiaxinyang
Calcite | Level 5

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
Diamond | Level 26

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

--
Paige Miller
jiaxinyang
Calcite | Level 5

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

SAS Innovate 2025: Register Now

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!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 5 replies
  • 876 views
  • 0 likes
  • 2 in conversation