topic Re: Creating table and counting instances of variables in SAS Programming

Creating table and counting instances of variables

bobbystweart12 — Sun, 14 Feb 2021 15:22:22 GMT

Re: Creating table and counting instances of variables

Reeza — Fri, 12 Feb 2021 21:14:02 GMT

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.

Re: Creating table and counting instances of variables

bobbystweart12 — Fri, 12 Feb 2021 21:20:13 GMT

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

Re: Creating table and counting instances of variables

mkeintz — Fri, 12 Feb 2021 21:20:44 GMT

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;

Re: Creating table and counting instances of variables

bobbystweart12 — Fri, 12 Feb 2021 21:34:49 GMT

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

Re: Creating table and counting instances of variables

mkeintz — Fri, 12 Feb 2021 21:40:58 GMT

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.