topic Re: How to output multiple variables from proc freq into a dataset? in SAS Procedures

How to output multiple variables from proc freq into a dataset?

corella — Fri, 27 May 2011 19:15:14 GMT

Hello!

I cannot figure out how to output results of proc freq on multiple variables into a SAS dataset. Proc freq can capture only the last variable listed in tables. I tried Proc summary but to no avail. I need to output the frequency of zero values into a dataset rather than having a huge .lst file because my original dataset has 399 variables and 1,700,000 records. I created a custom format for zero numeric values:
proc format;
value num_zero
0='ZERO'
other='NON-ZERO;
run;

Where do I go from this?

Thanks in advance if you can help me.

Message was edited by: corella

Re: How to output multiple variables from proc freq into a dataset?

Cynthia_sas — Fri, 27 May 2011 19:28:29 GMT

Hi:
Consider using the ODS OUTPUT statement.
[pre]
ods output onewayfreqs=work.owf;
proc freq data=sashelp.class;
tables age sex;
run;

ods listing;
proc print data=work.owf;
run;
[/pre]

produces this output in the WORK.OWF dataset:
[pre]
Cum Cum
Obs Table F_Age Age Frequency Percent Frequency Percent F_Sex Sex

1 Table Age 11 11 2 10.53 2 10.53
2 Table Age 12 12 5 26.32 7 36.84
3 Table Age 13 13 3 15.79 10 52.63
4 Table Age 14 14 4 21.05 14 73.68
5 Table Age 15 15 4 21.05 18 94.74
6 Table Age 16 16 1 5.26 19 100.00
7 Table Sex . 9 47.37 9 47.37 F F
8 Table Sex . 10 52.63 19 100.00 M M
[/pre]

cynthia

Re: How to output multiple variables from proc freq into a dataset?

SPR — Fri, 27 May 2011 19:37:25 GMT

Hello Corella,

You could try this:
[pre]
data i;
do i=1 to 100;
x=ROUND(RANUNI(12345));
y=ROUND(RANUNI(54321));
output;
end;
run;
data r (drop=i x--y);
set i end=e;
array a{*} x--y;
array a0{*} x1-x399;
do i=1 to DIM(a);
if a[i]=0 then a0[i]+1;
end;
if e then output;
run;
[/pre]
Sincerely,
SPR

Re: How to output multiple variables from proc freq into a dataset?

corella — Fri, 27 May 2011 20:45:02 GMT

Thank you Cynthia, it worked 95%. I still need to make some adjustments to the dataset I received but in general your method did the job.

Many thanks,
corella

Re: How to output multiple variables from proc freq into a dataset?

corella — Fri, 27 May 2011 20:53:44 GMT

SPR, I thought about creating a variable that would hold a rolled-up number of all zeros across the given row but I didn't know how to do that. I assume your code just does this. I want to use it, maybe not in this case but I have some difficulties to fully understand it. Would you please be so kind and add comments /* to each process because I don't want to just copy and paste, I want to understand what I am doing.

Thank you
corella

Re: How to output multiple variables from proc freq into a dataset?

Ksharp — Mon, 30 May 2011 09:12:34 GMT

[pre]

ods output onewayfreqs=work.owf;
proc freq data=sashelp.class ;
tables _all_/nocum nopercent ;
run;
data work.owf(drop=frequency) ;
set work.owf ;
count=frequency;
run;
data work.owf;
length v_name v_value $ 200;
set work.owf;
v_name=scan(table,2);
v_value=scan(catx(' ',of name--weight),1);
*name is the first variable,weight is the last variable in sashelp.class table;
keep v_name v_value count;
run;
[/pre]

Ksharp Message was edited by: Ksharp

Re: How to output multiple variables from proc freq into a dataset?

SPR — Mon, 30 May 2011 14:56:35 GMT

Hello Corella,

These are comments:
[pre]
data r (drop=i x--y); /* drops variables from x to y from output dataset */
set i end=e; /* e=1 for the last obs of dataset i */
array a{*} x--y; /* This array contains all variables from x to y in the dataset i */
array a0{*} x1-x399; /*This array contains variables from x1 to x399 for counts of zeros */
do i=1 to DIM(a); /*DIM(a) is the number of variables in array a */
if a[i]=0 then a0[i]+1; /* counts zeros for every variable in array a and store them in a0 */;
end;
if e then output; /* outputs result when the last observation from i is precessed */
run;
[/pre]
Sincerely,
SPR

Re: How to output multiple variables from proc freq into a dataset?

corella — Mon, 30 May 2011 17:08:21 GMT

Thanks for the comments SPR. What confused me before what the usage of i for both the dataset and counter. I needed a confirmation that i in set i is not the same as in do i=1 or a=0.

Regards,
corella

Re: How to output multiple variables from proc freq into a dataset?

corella — Mon, 30 May 2011 17:14:45 GMT

Ksharp,

Thanks for your code. Before I'll try it, I have a question:
catx(' ',of name--weight) - looks like this function captures missing character values while I need zero numeric ones. Do I change the code like catx(0,of name--weight)?

Regards,
corella

Re: How to output multiple variables from proc freq into a dataset?

SPR — Mon, 30 May 2011 17:37:02 GMT

It is OK. dataset and variable could have the same names.
SPR

Re: How to output multiple variables from proc freq into a dataset?

Ksharp — Tue, 31 May 2011 00:59:33 GMT

Yes.can code like catx('0',of name--weight),
But you need to test it by yourself.
SAS is smart enough to filter the missing value . when you use "scan(catx(" ,sas will automatically fetch the first null missing value.But you need to process your dataset firstly.
Like:
data temp;
....
_weight=put(weight, myfmt.);
.....

Ksharp Message was edited by: Ksharp

Re: How to output multiple variables from proc freq into a dataset?

corella — Wed, 01 Jun 2011 19:45:49 GMT

Thank you Ksharp.