topic Best option to transpose proc freq data in SAS Programming

Best option to transpose proc freq data

Maisha_Huq — Sun, 19 Apr 2015 23:22:41 GMT

Hi:

For tables I ve produced using proc SQL, I'd like to add a row of frequencies at the top of the table. Do you have suggestions for the best way for me to add this row to the table?

Is there a better way than to: use proc freq, then use data step processing to create a data set with just the row of frequencies I want (since, for example, proc transpose wouldn't seem to be helpful since the proc freq output data set wouldn't be multiple observations per ID/by variable)?

Current Proc SQL table:

Obs	Variable	estabgroupamp	estabgroupsst	estabgroupnur	estabgrouphih	F	PROB	pooledmean
1	var1	xx	xx	xx	xx	xx	xx	xx
2	var2	xx	xx	xx	xx	xx	xx	xx
3	var3	xx	xx	xx	xx	xx	xx	xx

I'd like to add a row of frequencies for estabgroupamp through estabgrouphih

So, used the following:

proc freq data=sample;

table estabgroup /out=freq_estabgroup;

run;

data freq_estabgroup1;

set freq_estabgroup;

variable="N";

if estabgroup="amp" then N_estabgroup_amp=COUNT;

if estabgroup="sst" then N_estabgroup_sst=COUNT;

if estabgroup="hih" then N_estabgroup_hih=COUNT;

if estabgroup="nur" then N_estabgroup_nur=COUNT;

drop estabgroup count percent;

run;

But of course I get:

Is there a better way?

Thank you in advance

Re: Best option to transpose proc freq data

art297 — Sun, 19 Apr 2015 23:56:10 GMT

You didn't show what your table looked like prior to running proc freq but, given your proc freq output, you could always use something like:

/*create test data*/

data freq_estabgroup;

input variable $

N_exemplar_Control

N_exemplar_CurrentOSHA

N_exemplar_Dialogue

N_exemplar_Furture

N_exemplar_MythFact;

cards;

N 64608 . . . .

N . 6802 . . .

N . . 6802 . .

N . . . 10285 .

N . . . . 10207

;

data freq_estabgroup1;

update freq_estabgroup (obs=1) freq_estabgroup;

by variable;

run;

Re: Best option to transpose proc freq data

Reeza — Mon, 20 Apr 2015 00:22:58 GMT

Maisha_Huq wrote:

Did you try a proc transpose?

Re: Best option to transpose proc freq data

Maisha_Huq — Mon, 20 Apr 2015 16:54:56 GMT

Thank you both! I ended up going with some data step processing