Converting proc freq output into a dataset

Question

I have a sas data set with 564 variables. I need to create a new table with three columns, column1 will be the variables name , column2 will be the value of that variable, and column3 will be observation number. So if I have a variable gender then gender will be listed 2 times in the variable column, and the gender values will be listed as m in the first row and female in the second row, and the column3 will be just the number of the observation. This is how it should look. Many thanks in advance.

var    value obs
gender m      1
gender f      2
ans    yes    3
ans    no     4

In the future, this sort of question is better asked by providing sample data and the code you're using to run frequencies on it. — Joe

Joe Joe · Accepted Answer · 2016-03-22T16:09:04

The key to getting a table out of PROC FREQ that has all the values in one column is ods output combined with coalescec.

ODS OUTPUT lets you tell PROC FREQ to put everything into one dataset (as opposed to out= which just puts one Freq table into one dataset). That gives you a slightly messy result, which we then use coalescec to fix. That function takes a list of variables and returns the first nonmissing value from them; since the F_ variables always have just one value populated (the formatted value of the variable in the table), it's easy to use them.

ods output onewayfreqs=freqs;
proc freq data=sashelp.class;
  tables age sex;
run;
ods output close;  *technically unneeded but makes it more clear;

data want;
  set freqs;
  value = left(coalescec(of f_:));
run;

The rest of what you note above is trivial from that dataset.

Converting proc freq output into a dataset

1 Answers