Creating table and counting instances of variables

[Reeza]

Is that sample table your input data? Please show clearly, what your input data looks like and preferably, the exact matching output you'd expect.

[bobbystweart12]

The table would be the preferred output.

The actual input is millions of lines but simply it is a year and then a number 1 - 12.  I put a very small excerpt from it below

Year	Famafrench
2018	1
2019	1
2020	1
2018	3
2019	3
2020	3
2018	5
2019	5
2020	5
2018	5
2019	5
2020	5
2018	6
2019	6
2020	6
2018	7
2019	7
2020	7

[mkeintz · Accepted solution]

Are you counting the number of companies, by fiscal year, for each famafrench classification?   If so, and if you have exactly one record per company per year, then you can do a proc freq, as in:

proc freq data=test;
  tables fyear * famafrench /out=annual_ff_freqs (drop=percent);
run;

This will print a tabulation that looks like your table, and output a data set, with one record per cell, containing variables FYEAR, FAMAFRENCH, and COUNT.  

Now, if you want a data set that looks like your table (one obs per year, and one var per ff), then apply proc transpose to the proc freq output:

proc transpose data=annual_ff_freqs out=want (drop=_name_ _label_);
  by fyear;
  id famafrench;
  var count;
run;

[bobbystweart12]

Thank you the second one worked perfectly. Is there a way to do the same if famafrench was instead a string?

[mkeintz]

Proc freq treats all variables as nominal variables, so they can be numeric or character.

But more in the spirit of learning to use a software product for a project, this is exactly the kind of question that begs to be answered by trial - which would be rewarded with success in this case.