topic Re: What better than a proc freq !! in ODS and Base Reporting

What better than a proc freq !!

anoopm7 — Mon, 21 Jul 2014 19:37:18 GMT

I need to find out if some specific numeric values are there in a dataset (have millions of records) for a particular field lets say 'city'. One way of doing this is to do a freq on that variable (called as city) and then compare the output and if there is match do print the details out. But I don't want to do freq as its time consuming. Is there a better way to achieve the results ?

Re: What better than a proc freq !!

ballardw — Mon, 21 Jul 2014 20:05:50 GMT

Depending on what you need something like this can give you count of the instances of specific values

proc sql;

select variable, Count(*) as valuecount

from dataset

where variable in (value1, value2, value3)

group by variable;

quit;

if the values are strings then enclose them in quotes but the results are case sensitive unless you use Upcase or lowcase such as upcase(variable) in (value list);

Re: What better than a proc freq !!

Reeza — Mon, 21 Jul 2014 20:36:09 GMT

Assuming that the values are in a tables

PROC SQL;

Create Table Match as

select Variable

from TableA

where Variable in (Select Variable from TableB)

Order by variable;

quit;

*Non matches;

PROC SQL;

Create Table Non_Match as

select Variable

from TableA

where Variable not in (select Variable from TableB)

order by variable;

quit;

Re: What better than a proc freq !!

Astounding — Mon, 21 Jul 2014 21:10:53 GMT

The real answer depends on what you really need. For example, do you need to know whether CITY is ever "Boston" on some records, and "New York" on others? Or do you need to know whether CITY is ever either "Boston" or "New York" on any record? At any rate, here's a much faster tool to get a yes/no answer for a single CITY value:

%let city=N;

data _null_;

set have (keep=city);

where city="Boston";

call symputx('city', 'Y');

stop;

run;

It stops processing as soon as it finds a single "Boston" in the data, so you don't run up much of a bill. Then you can use the macro variable &CITY later on in the program. But it all depends on what you really need.

Re: What better than a proc freq !!

FriedEgg — Wed, 23 Jul 2014 17:22:00 GMT

The same functionality denoted by can be accomplished with PROC SQL as well by using the INOBS=1 option

You will get a boolean response in the macro variable assigned

20 proc sql noprint inobs=1;

21 select count(*) into :acura from sashelp.cars where make='Acura';

WARNING: Only 1 records were read from SASHELP.CARS due to INOBS= option.

22 quit;

24 %put &acura;

20 proc sql noprint inobs=1;

21 select count(*) into :acura from sashelp.cars where make='Foo';

WARNING: Only 1 records were read from SASHELP.CARS due to INOBS= option.

22 quit;

24 %put &acura;

Re: What better than a proc freq !!

jakarman — Wed, 23 Jul 2014 19:10:58 GMT

There are lot better ways to do that. Most of them are requiring you know something about your data and the effects on the system of your code.

When your data is in an external DBMS it will be different to a local SAS-dataset

Is your data pre-sorted or indexed on that field or must you process all data in a sequential way? With this: will the processing occur often or just once.

Some procedures are multithreading others are not. Are you having benefits of the multithreading or will the IO pose a new bottleneck?

Re: What better than a proc freq !!

anoopm7 — Thu, 24 Jul 2014 20:56:07 GMT

I did it as below

proc sql;

select count(*) into:nobs from test&i.;

quit;

%if &nobs> 0 %then %do ;

data princheck&i. ;

set "&&fln&i." ;

where TTN in ('9038')

run;