LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES

[Noomen]

Hi all, I need help running the loop. It works only for the first city.

I need each city in a separate file with its length.

DATA have;
city="TORONTO";L=LENGTH(CITY); OUTPUT;
city="SEATTLE";L=LENGTH(CITY); OUTPUT;
city="BOSTON";L=LENGTH(CITY); OUTPUT;
city="MIAMI";L=LENGTH(CITY); OUTPUT;
RUN;

proc sql noprint; SELECT CITY INTO: CITY_list FROM HAVE; QUIT;

%macro LOOP1;
%local i next_name;
%do i=1 %to %sysfunc(countw(&CITY_list));
%let next_name = %scan(&CITY_list, &i);
data &NEXT_NAME ; SET have ;
if prxmatch ("/\&NEXT_NAME/", CITY) then flag=1; if flag=1;
KEEP CITY L;
RUN;
%let i = %eval(&i + 1);
%end;
%MEND;

%LOOP1;

[Kurt_Bremser]

The initial issue is that you need to use SEPARATED BY in the SQL SELECT:

proc sql noprint;
select city into :city_list from have;
quit;

But there are other issues as well.

Why do you create flag in the data step when you do not keep it?

And you do not need the macro loop. Define the macro with next_name as parameter, and use CALL EXECUTE in a DATA _NULL_ step from dataset have to call the macro for each city.

[Noomen]

Thanks for your questions. Actually, my real dataset is much more complicated (I work for the Federal Department of Transport), hence the extra variables. I just tried to narrow down the question to the loop I was planning to build..

[SASKiwi · Accepted solution]

Why complicate your macro with a loop? Just call your macro for each city:

%macro MyMacro (Next_Name = );

data &NEXT_NAME ; SET have ;
if prxmatch ("/\&NEXT_NAME/", CITY) then flag=1; if flag=1;
KEEP CITY L;
RUN;

%mend MyMacro;

DATA have;
city="TORONTO";L=LENGTH(CITY); OUTPUT;
city="SEATTLE";L=LENGTH(CITY); OUTPUT;
city="BOSTON";L=LENGTH(CITY); OUTPUT;
city="MIAMI";L=LENGTH(CITY); OUTPUT;
RUN;

data _null_;
  set have;
  call execute('%MyMacro (Next_Name =' !! trim(city) !! ')');
run; 

[Noomen]

Thanks but it fails if the city name has a blank (like SAN DIEGO, LOS ANGELES, etc. ).
What would be the fix in the prxmatch parameters to allow the macro to run ?

[Tom]

---

@Noomen wrote:
Thanks but it fails if the city name has a blank (like SAN DIEGO, LOS ANGELES, etc. ).
What would be the fix in the prxmatch parameters to allow the macro to run ?

---

Do NOT use the CITY name as the name of the DATASET.  

Add some other argument to your macro to specify the name of the dataset to be created.

Also why are you using regular expressions? 

%macro next(ds=,city=);
data &DS ;
  SET have ;
  where city = "&city";
  ...