SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Noomen
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Noomen
Fluorite | Level 6

Thank you for the syntax for the sysfunc is

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

Tom
Super User Tom
Super User

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 621 views
  • 0 likes
  • 3 in conversation