topic Re: Proc Freq or Proc SQL in SAS Programming

Proc Freq or Proc SQL

trt3s — Fri, 14 Oct 2016 19:34:30 GMT

New SAS Programmer, and I am currently using Proc Freq to calculate the percent and count of two variables in a dataset and it works great. However, I need to only display the events where there percent is greater than 15. I am assuming I should use a proc sql so that I can create a table and then use that created dataset to write if 'variable' lt .15 delete or something to that nature. However, I am not very familar with proc sql or prox freq for that matter, and maybe there is a way to do this in proc freq but I am not sure how. I would 'prefer' to use PROC Freq over Proc SQL because I understand it better, but that is just my preference. My current syntax reads;

proc freq data=table2;
table SOC CODE / nocum;
run;

any ideas?

Re: Proc Freq or Proc SQL

Reeza — Fri, 14 Oct 2016 19:38:25 GMT

Either way you're going to go through the same process - create a table with results and then filter and print it out. I think it's a bit simpler in proc freq since you already have the calculations completed.

I have some code here that processes a proc freq table and generates the output in a formatted form, you can add a WHERE clause to the last table to filter your results.

https://gist.github.com/statgeek/e0903d269d4a71316a4e

Re: Proc Freq or Proc SQL

trt3s — Fri, 14 Oct 2016 19:42:07 GMT

I'm not sure I follow that code..

Re: Proc Freq or Proc SQL

Astounding — Fri, 14 Oct 2016 19:53:39 GMT

Here's a simplified version of what should happen. It assumes that the percents should be based on the total count of all observations, not on the subset of those with a percent > 15%.

proc freq data=have;

tables soc / noprint out=soc_stats;

tables code / noprint out=code_stats;

run;

proc print data=soc_stats;

where percent > 15;

var soc count percent;

run;

***Looks like I was 34 seconds slower than ballardw.

proc print data=code_stats;

where percent > 15;

var code count percent;

run;

You may have to double-check on the right cutoff for the WHERE statement. I believe that 15 is correct, but you may need to change it to 0.15 instead.

Re: Proc Freq or Proc SQL

ballardw — Fri, 14 Oct 2016 19:52:40 GMT

Maybe simpler though less complete:

proc freq data=table2 noprint;
   tables SOC  / nocum out=SOCFreq;     /*<= creates output summary set*/
   tables CODE / nocum out=CodeFreq;
run;

proc print data= SOCFreq noobs;
   where percent ge 15;    /*<= Filter on percent*/
run;

proc print data= CodeFreq noobs;
   where percent ge 15;    /*<= Filter on percent*/
run;

Note the Freq only creates an output data set for one-way table this way for the last variable on each tables statement, so there are two tables, one to create each output data set.

Re: Proc Freq or Proc SQL

trt3s — Fri, 14 Oct 2016 19:55:30 GMT

Thank you! both work!

Re: Proc Freq or Proc SQL

trt3s — Fri, 14 Oct 2016 19:56:16 GMT

I am sure this works great as well, but I am not yet able to follow that code. Thank you though!