Programming the statistical procedures from SAS

Frequency and SUMmation for prior six months

Reply
Occasional Contributor
Posts: 10

Frequency and SUMmation for prior six months

[ Edited ]

Hello, Friends, i have two questions; I have attached a sample data file.

I am using daily data, which has a number of firms and years. and i am looking to calculate following two things for each firm.

1) I want to calculate the frequency of a variable for last six month. I am using following data which can calculate over a month. can some one suggest how can i include prior 6 months>? 

 

proc freq data=dailypa;
tables Firm * year * month / noprint out=monthly_counts ;run;

 

2) my second question is also similar except i want to get a summation of a variable over prior six-month. The following code is useful for a month only.

proc summary data=illiquid nway;
class Firm year month;
var XYZ;
output out=summary_data (keep=firm year month total_ratio) sum=total_ratio; run;

 

Any help will be appreciatable. 

 

Esteemed Advisor
Posts: 7,056

Re: Freq and SUM

You didn't provide an example of what your data look like, but you can always include a where option (e.g., data=dailypa(where=(..))

If your data includes a date variable, you could specify something like where=date between intnx('month',today(),-6,'s') and today() ))

 

Otherwise, you could use the combination of year= and month in

 

And, if you want those calculations to be overall, rather than by month, simply leave month out of your class and table specs.

 

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 10

Re: Freq and SUM

I have added a EXCEL file of sample data and want a summation of XYZ variable for prior six months. The frequency of date variables.
Thank you
Esteemed Advisor
Posts: 7,056

Re: Freq and SUM

Your example doesn't contain a variable called firm, your second question pertains to a different dataset, and I'm still not sure what level of summary you're looking for or how you are defining the previous 6 month.

 

Are you looking for something like:

proc import datafile='/folders/myfolders/sample.xlsx' out=dailypa dbms=xlsx replace;
run;

proc freq data=dailypa (where=(date between intnx('month',today(),-7,'b') and
                                            intnx('month',today(),-1,'e') ));
  tables permno * year * month / noprint out=monthly_counts ;
run;

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 10

Re: Freq and SUM

I think i failed to made my point earlier.
Firm is permno is the file.
the code you wrote calculate it month wise, like
Month count want_Variable
1 12
2 10
3 5
4 4
5 20
6 10
7 61 (sum of six previous month)

i hope its clear now.

Esteemed Advisor
Posts: 7,056

Re: Freq and SUM

Still not clear as there are a number of ways to achieve what you showed. Do you want something like:

 

proc import datafile='/folders/myfolders/sample.xlsx' out=dailypa dbms=xlsx replace;
run;

proc freq data=dailypa (where=(date between intnx('month',today(),-7,'b') and
                                            intnx('month',today(),-1,'e') ));
  tables permno * year * month / noprint out=monthly_counts ;
run;

proc print data=monthly_counts;
  sum count;
  by permno;
run;

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 5 replies
  • 71 views
  • 0 likes
  • 2 in conversation