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;
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;
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.
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 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";
...
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: