BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
corella
Calcite | Level 5
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
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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

View solution in original post

11 REPLIES 11
Cynthia_sas
SAS Super FREQ
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
corella
Calcite | Level 5
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
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
corella
Calcite | Level 5
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
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
corella
Calcite | Level 5
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
SPR
Quartz | Level 8 SPR
Quartz | Level 8
It is OK. dataset and variable could have the same names.
SPR
Ksharp
Super User
[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
corella
Calcite | Level 5
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
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 39754 views
  • 2 likes
  • 4 in conversation