topic LOOP a values in a LIKE statement in SAS Programming

LOOP a values in a LIKE statement

Noomen — Wed, 07 Oct 2020 14:27:57 GMT

Hi all,

I have a file having 6 company names and I want to loop through the names with like to return values from another file (phone number, email, etc). The first problem is I have to loop through the file to get the company names; they are entered kind of manually in the macro now.

The second problem is the syntax in the where statement.

%macro Rollthecomp;
%let condition1=GYSERVICESINC;
%let condition2=MECH;
%let condition3=ALCOOP;
%let condition5=RTAGESYSTEMS;
%let condition4=SCHEMICALS;
%let condition6=IORPROPANE;

%do i =1 %to 6;
proc sql;
create table listcarriers&&i as select phonenumber, email
from mycompanyfile
where compress(uniquename) like "&&condition&&i%";
quit;
%end;
%mend;

Re: LOOP a values in a LIKE statement

PaigeMiller — Wed, 07 Oct 2020 14:50:14 GMT

It's not completely clear what you are doing, but you don't have loop through a file to get company names.

proc sql noprint;
    select distinct companynames into :names separated by '~' from yourdataset;
quit;

This creates a macro variable called &NAMES with all of the unique company names in it, separated by a tilde (~)

Then, do the looping like this (naturally, this is untested code)

%macro dothis;
    proc sql;
        create table carriers as select phonenumber, email
        from mycompanyfile
        where (
        %do i=1 %to %sysfunc(countw,&names,~);
            compress(uniquename) like "%scan(&names,&i,~)"
            %if &i<%sysfunc(countw,&names,~) %then or;
         %end;
         );
    quit;
%mend;
%dothis

This creates one large file with all of the companies found, rather than many smaller files for each company. I strongly recommend you handle it via one large file and then use BY statements or WHERE clauses when you want to analyze the individual companies.

Re: LOOP a values in a LIKE statement

Tom — Wed, 07 Oct 2020 15:22:49 GMT

Why did you double the &'s?

where compress(uniquename) like "&&condition&&i%"

When the macro processor sees two &'s it will convert that to one and then re-scan for macro variable references. So this will be evaluate the && to & which will result in &condition&i which look for the value of a macro variable named CONDITION and then append the value of I to the result.

Instead you want:

where compress(uniquename) like "&&condition&i%"

Which will evaluated in this order. && -> & and &i -> 1. Which will result in &condition1 which will then be evaluated.

Re: LOOP a values in a LIKE statement

Noomen — Thu, 08 Oct 2020 16:13:49 GMT

Thank you for the syntax for the sysfunc is

%do i=1 %to %sysfunc(countw(&names,~));