- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the syntax for the sysfunc is
%do i=1 %to %sysfunc(countw(&names,~));
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.