BookmarkSubscribeRSS Feed
Kyojik
Obsidian | Level 7

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. 

 

5 REPLIES 5
art297
Opal | Level 21

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

Kyojik
Obsidian | Level 7
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
art297
Opal | Level 21

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

 

Kyojik
Obsidian | Level 7
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.

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

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