topic Re: LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES in SAS Programming

LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES

Noomen — Wed, 18 May 2022 03:25:01 GMT

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;

Re: LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES

Kurt_Bremser — Wed, 18 May 2022 03:46:33 GMT

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.

Re: LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES

SASKiwi — Wed, 18 May 2022 03:52:00 GMT

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;

Re: LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES

Noomen — Wed, 18 May 2022 15:58:43 GMT

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 ?

Re: LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES

Noomen — Wed, 18 May 2022 18:16:47 GMT

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..

Re: LOOP TROUGH A LIST of STRINGS TO CREATE DIFFERENT FILES

Tom — Wed, 18 May 2022 19:29:24 GMT

@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";
  ...