topic Re: Freq and SUM in Statistical Procedures

Frequency and SUMmation for prior six months

Kyojik — Thu, 15 Jun 2017 20:42:14 GMT

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.

Re: Freq and SUM

art297 — Thu, 15 Jun 2017 20:25:38 GMT

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

Re: Freq and SUM

Kyojik — Thu, 15 Jun 2017 20:41:33 GMT

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

Re: Freq and SUM

art297 — Thu, 15 Jun 2017 21:53:50 GMT

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

Re: Freq and SUM

Kyojik — Thu, 15 Jun 2017 22:02:30 GMT

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.

Re: Freq and SUM

art297 — Thu, 15 Jun 2017 22:17:48 GMT

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