topic Re: check if variables from a list exist in a table in SAS Programming

check if variables from a list exist in a table

MART1 — Wed, 05 Jan 2022 17:58:01 GMT

Hello

I need to check a table against a list of variables, to identify those records in the table where any of the variables is contained.

In my dummy example below, I need to identify those records in SASHELP.CARS where either "coupe", "hatch", "quattro" or "caravan" is contained in MODEL.

This works fine but in my real data I have about 13 million records, with about 8 million variables; SAS always times out before it completes the task.

I was hoping there would be a better way to do this?

many thanks

  /*create lookup table with keys to be searched*/

data LOOKUP;
input KEY $;

datalines;
coupe
hatch
quattro
caravan
;
run;

/*create table where output will be saved*/

PROC SQL;
CREATE TABLE OUTPUT
(
MAKE char (20),
MODEL char (40)
);

%macro makereport(Var=);

/*marco loops*/

PROC SQL;
CREATE TABLE OUTPUT_AUX AS
    SELECT MAKE, MODEL
FROM SASHELP.CARS
WHERE UPPER(MODEL) contains UPPER(&Var);

QUIT;

/*append result to output table*/

PROC APPEND BASE=OUTPUT DATA=OUTPUT_AUX FORCE;

RUN;

%mend makereport;

data _null_;
                set WORK.LOOKUP;
                string = cats('%makereport(Var="',KEY,'");');
                call execute(string);
run;

Re: check if variables from a list exist in a table

PaigeMiller — Wed, 05 Jan 2022 18:06:46 GMT

PROC SQL;
    CREATE TABLE OUTPUT_AUX AS SELECT MAKE, MODEL
    FROM SASHELP.CARS
    WHERE upcase(MODEL) in (select upcase(key) from lookup);
QUIT;

No macros needed. I can't test it on a data set that large, but 13 million records shouldn't really be problem these days. It's not clear why you mention you have 8 million variables, as those are not used in this problem at all.

Re: check if variables from a list exist in a table

MART1 — Wed, 05 Jan 2022 18:15:42 GMT

Thanks @PaigeMiller

It does not work though; it must be a CONTAINS rather than IN.

I mentioned the 8 million to give an idea of the size (of course I cannot paste here); when I run it I get an "out of memory " type message.

thanks

Re: check if variables from a list exist in a table

ballardw — Wed, 05 Jan 2022 18:16:59 GMT

Your description of "I need to identify those records in SASHELP.CARS where either "coupe", "hatch", "quattro" or "caravan" is contained in MODEL" is a "Check if VALUES" from a list exist.

It would help to show what you expect but consider:

proc sql;
   create table found as
   select b.make, b.model
   from lookup as a, sashelp.cars as b
   where find(b.model, a.key,'i')>0;
run;

The above code compares all records in the lookup data set with the SASHELP.Cars (the comma between the alias A and the Sashelp.cars data set name) and when the string of the Key in the lookup is found in the Model variable, ignoring case (the 'i' in the Find function) keeps the make and model.

Note: if your KEY could possibly appear twice in a single Model you would add the Distinct predicate to the Select to get only one result per Make/model combination

proc sql;
   create table found as
   select distinct b.make, b.model
   from lookup as a, sashelp.cars as b
   where find(b.model, a.key,'i')>0;
run;

Re: check if variables from a list exist in a table

PaigeMiller — Wed, 05 Jan 2022 18:34:43 GMT

Probably the code from @ballardw is better, but still a Cartesian join in SQL with 13 million records may be slow. Whether it is slower than the solution I present is unknown.

%macro dothis;
proc sql noprint;
    select quote(trim(key)) into :keys separated by ' ' from lookup;
quit;

data want;
    set sashelp.cars;
    if %do i=1 %to %sysfunc(countw(&keys));
        find(model,%scan(&keys,&i,%str( )),'i')>0
        %if &i<%sysfunc(countw(&keys)) %then or;
        %end;
    then output;
run;
%mend;
%dothis

Re: check if variables from a list exist in a table

yabwon — Wed, 05 Jan 2022 19:52:33 GMT

Maybe data step array could be solution?

data LOOKUP;
input KEY $;
datalines;
coupe
hatch
quattro
caravan
;
run;

data _null_;
  call symputX("N",nobs,"G");        
  stop;
  set lookup nobs=nobs;
run;




data want;
    /* load keys int an array */
    array lookup[&N.] $ 12 _temporary_;
    do _N_ = 1 by 1 until(end);
      set lookup end=end;
      lookup[_N_] = KEY; drop KEY;
    end; 

    /* loop over data set */
    do until(EOF);
      set sashelp.cars end=EOF;

      do _N_ = 1 to &N.;
        if find(model,lookup[_N_],'it') then 
          do; /* if model contains key do output... */
            output;
            leave; /* ...and leave the loop since there is no point to check next */
          end;
      end;
    end;

  stop;
run;

Everything in one data step with no macro loops.

Bart

Re: check if variables from a list exist in a table

ballardw — Wed, 05 Jan 2022 20:00:07 GMT

@MART1 wrote:

Thanks @PaigeMiller

It does not work though; it must be a CONTAINS rather than IN.

I mentioned the 8 million to give an idea of the size (of course I cannot paste here); when I run it I get an "out of memory " type message.

thanks

How many of the 8 million variables are actually involved in the comparison? Reduce the size to what is actually needed such as the comparison variables and enough others to uniquely identify each record. Then if needed combine that back to the original data.

Also, how many distinct values of the comparison variables are involved?

How many variables are compared to the look up values? Just how many look up values are there?

And perhaps if the values of the variables aren't sensitive show some actual examples of what you are searching for in some actual values.

Re: check if variables from a list exist in a table

ghosh — Wed, 05 Jan 2022 20:06:35 GMT

In your first line if you mean "values" rather than "variables" then the following will likely work in an 8 million record database. A macro variable can hold about 65K so you could fit in a large amount of values


proc sql noprint;                              
 select key into :to_inc separated by '|'
 from lookup;
quit;
%put &=to_inc;

Data want;
length match 3;
 Set sashelp.cars;
    if prxmatch("/&to_inc/i",model) 
 	Then MATCH=1;
 	else MATCH=0;
if match;
run;

Re: check if variables from a list exist in a table

SASKiwi — Wed, 05 Jan 2022 21:01:32 GMT

Here's another efficient way that doesn't have the limitations of lists:

data LOOKUP;
input start $;
FmtName = '$Lookup' ;
if _n_ = 1 then do;
  HLO = 'O';
  label = 'N';
  output;
end;
HLO = '';
label = 'Y';
output;
datalines;
SEDAN
WAGON
SUV
;
run;

proc format cntlin = LOOKUP;
run;

data want;
  set SASHELP.CARS;
  where put(upcase(type), $Lookup.) = 'Y';
run;

Re: check if variables from a list exist in a table

andreas_lds — Thu, 06 Jan 2022 06:02:03 GMT

You are mixing terms, making it difficult to follow the problem description. You are talking about finding variables in dataset, but actually it seems that you want to check if one/some/all variables contain one value of list of values.

To make our lives simpler, please post some observations of the data you have as working sas code (data step with datalines). The code posted by @yabwon seems to solve the problem.

Re: check if variables from a list exist in a table

MART1 — Thu, 06 Jan 2022 16:39:12 GMT

Thanks @andreas_lds

In the "real world", my dataset contains a list of users (column A) with their associated address (column B). The address may or may not contain the postcode. This dataset contains about 13M records.

In another dataset I have a list of all UK postcodes (distinct list); it's about 8M records.

The goal is to identify those users in the first dataset who have a postcode (any postcode) in their address.

Translated to my dummy example, I wanted to find those records in CARS where model contain either "quattro" or "coupe"...

If it does, the record needs to be added to the final output.

I have used @yabwon code; it works perfectly if I use a small subset of the postcode, but when I use the full list (8M) it takes a long time (tried a few times but I had to stop it after 3 hours).

Maybe I'm just too "demanding" and this would take quite some time?

Thanks

Re: check if variables from a list exist in a table

MART1 — Thu, 06 Jan 2022 16:46:33 GMT

Thanks @ghosh

unfortunately the length of the macro exceeds 65k (maybe a can try breaking up)

thanks

Re: check if variables from a list exist in a table

MART1 — Thu, 06 Jan 2022 17:08:39 GMT

Thanks @PaigeMiller

unfortunately the macro "keys" exceeds the maximum length (65k)
thanks

Re: check if variables from a list exist in a table

ghosh — Thu, 06 Jan 2022 17:35:21 GMT

Yes, with this approach you could break up the list. How may "words" do you have in your lookup list.

Frankly if I were you I would try the proc format approach shown by @SASKiwi
It's also described in this paper, see p.2
https://support.sas.com/resources/papers/proceedings/proceedings/sugi26/p102-26.pdf

Re: check if variables from a list exist in a table

MART1 — Thu, 06 Jan 2022 17:50:55 GMT

Hi @ghosh

I tried to break it up, but in order to work it would need far too many partitions (the lookup list contains all UK postcodes, 8 M "words", each 6 or 7 digits).

I'll try the proc format approach (never seen it before but I'll give it a go!)

thanks

Re: check if variables from a list exist in a table

yabwon — Thu, 06 Jan 2022 17:51:25 GMT

Some ideas for performance improvement (sorry that without details, but I'm out of town and writing from mobile):
1) are you able to sort dataset with codes by "town size", i.e. a postal code from London should cover bigger number of users, so if it is at the beginning of the array it should exit the do-loop faster.
2) try to load adres column into second array (so both data and lookup will be in memory). Array index will be pointing observation number in the dataset so you can use it in second data pass.
3) split lookup into several smaller datasets and run several concurrent sessions.
4) does the postal code have some form (e.g. 12-345) ? So you could use regexp to narrow down list of addresses.

Bart

Re: check if variables from a list exist in a table

andreas_lds — Fri, 07 Jan 2022 06:00:20 GMT

@MART1 wrote:

Thanks @andreas_lds

In the "real world", my dataset contains a list of users (column A) with their associated address (column B). The address may or may not contain the postcode. This dataset contains about 13M records.

In another dataset I have a list of all UK postcodes (distinct list); it's about 8M records.

The goal is to identify those users in the first dataset who have a postcode (any postcode) in their address.

[...]

Maybe I'm just too "demanding" and this would take quite some time?

Thanks

Yes, this will take some time. So i would not use the postcodes dataset in the first step, but a regular expression to verify that the address contains (most likely) a postcode. I am not that familiar with uk postcodes, so i can't suggest a perfect expression right know, this one may work:

.*([a-z]{1,2}\d[a-z]? \d[a-z]{2}).*

In a second step i would load the postcode dataset into a hash object to verify that the result of the regex is an existing postcode.

Re: check if variables from a list exist in a table

Kurt_Bremser — Fri, 07 Jan 2022 08:39:03 GMT

You can try two approaches:

find a way to extract the postcode from the address field, so you can join with that value or use a hash object loaded from the lookup postcodes
load the lookup postcodes into an array and loop over it to perform a FINDW or similar function for each entry

We would need to see complete examples for your address variable, covering all structures you have in there.

Re: check if variables from a list exist in a table

Patrick — Fri, 07 Jan 2022 11:05:47 GMT

@MART1

Your "real world" explanation really helps to better understand the challenge. Some representative sample data provided via a SAS data step would likely get us even closer to your reality.

Below code based on what I found on the Internet how UK postcodes look like: https://ideal-postcodes.co.uk/guides/uk-postcode-format

The code syntax to extract the codes from a string based on SAS code sample from here.

Below code may not be fully suitable for your data but it should give you the necessary pointers ...and depending on your hardware the code should execute within minutes (or less).

...and I just realized that I've done things only for the Outward portion of the UK postcode. To change things for the full postcode shouldn't be that hard though. If you want SAS code for it then please provide representative sample data in the form of working data step code creating this data (the "users" and "postcode" tables).

data users;
  infile datalines truncover dlm='|';
  input user $ addr $40.;
  datalines;
aa|aaad bbbd 123 xxx ab3x blah
aa|aaad bbbd 2 xxx c9 blah
aa|aaad bbbd xc9 xxx  ab3x blah
aa|aaad bbbd c9 xxx  ab3x blah
aa|aaad bbbd c999 xxx  ab3xy blah
;

data postcodes;
  input postcode $;
  datalines;
ab3x
c9
;
data want;
  if _n_=1 then
    do;
      if 0 then
        set postcodes(keep=postcode);
      dcl hash h1(dataset:'postcodes');
      h1.defineKey('postcode');
      h1.defineDone();
    end;
  call missing(of _all_);

  set users;
  array postcode_candidate_ {4} $4;
  length _found $4;

  _ExpressionID = prxparse('/\b[[:alpha:]]{2}\d[[:alpha:]]\b|\b[[:alpha:]]\d[[:alpha:]]\b|\b[[:alpha:]]\d\b|\b[[:alpha:]]\d\d\b|\b[[:alpha:]]{2}\d\b|\b[[:alpha:]]{2}\d\d\b/');
  _start = 1;
  _stop = length(addr);

  /* Use PRXNEXT to find the first instance of the pattern, */
  /* then use DO WHILE to find all further instances.       */
  /* PRXNEXT changes the _start parameter so that searching  */
  /* begins again after the last match.                     */
  call prxnext(_ExpressionID, _start, _stop, addr, _position, _length);
  do while (_position > 0);
    _found = substr(addr, _position, _length);
    if h1.check(key:_found)=0 then
      do;
        _i=sum(_i,1);
        postcode_candidate_[_i]=_found;
      end;
    call prxnext(_ExpressionID, _start, _stop, addr, _position, _length);
  end;
  drop _:;
run;