The SAS Output Delivery System and reporting techniques

What better than a proc freq !!

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

What better than a proc freq !!

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 ?


Accepted Solutions
Solution
‎07-21-2014 04:05 PM
Super User
Posts: 11,134

Re: What better than a proc freq !!

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);

View solution in original post


All Replies
Solution
‎07-21-2014 04:05 PM
Super User
Posts: 11,134

Re: What better than a proc freq !!

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);

Super User
Posts: 19,157

Re: What better than a proc freq !!

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;

Super User
Posts: 5,366

Re: What better than a proc freq !!

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.

Trusted Advisor
Posts: 1,301

Re: What better than a proc freq !!

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;

23        

24         %put &acura;

1

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;

23        

24         %put &acura;

0

Valued Guide
Posts: 3,208

Re: What better than a proc freq !!

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?  

---->-- ja karman --<-----
Occasional Contributor
Posts: 16

Re: What better than a proc freq !!

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 988 views
  • 7 likes
  • 6 in conversation