Hi,
I am trying to scan a list of variables which are in a macro. When I did harcoding it is working fine and when I am extracting them from a proc sql select into, I am not getting expected result.
Success one(hardcode): It is perfectly giving output
%let type_list = %str("Hybrid","SUV","Sedan","Sports","Truck","Wagon");
%macro type_c;
%do i = 1 %to %sysfunc(count(&type_list.,%str(,))) +1;
%let type = %scan(&type_list.,&i.,',');
%put &i. is &type.;
%end;
%mend;
%type_c;
ISSUE:
proc sql;
select distinct quote(strip(type)) into :typ_list separated by "," from sashelp.cars;
quit;
%put &typ_list.;
%macro type_c2;
%do i = 1 %to %sysfunc(count((&typ_list.),%str(,))) +1;
%let type2 = %scan((&typ_list.),&i.,',');
%put &i. is &type2.;
%end;
%mend;
%type_c2;
This one is giving output
1 is ("Hybrid"
2 is "SUV"
3 is "Sedan"
4 is "Sports"
5 is "Truck"
6 is "Wagon")
the 1st one and 6th one are getting braces in their output which I don't want.
if I give statement by removing braces aroung &typ_lst. in scan function, I am getting error.
%let type2 = %scan(&typ_list.)&i.,',');
%scan and %sysfunc(scan) giving same result.
I want output of 1st and 6th one without braces like rest all.
Your code shows
%let type2 = %scan((&typ_list.),&i.,',');
and so when you put parentheses around &typ_list, this is just adding the text ( at the left and adding the text ) at the right of &typ_list, which then gives ("Hybrid","SUV","Sedan","Sports","Truck","Wagon") as the argument to the %SCAN function. So the %SCAN function finds the first text string as ("Hybrid"
I think what you want is this:
%let type2 = %scan(%bquote(&typ_list.),&i.,',');
The %quote masks the commas inside &typ_list so they are not used as a separator of different arguments to %SCAN, and the parentheses in %BQUOTE( ) are not interpreted as being added to the text string.
Your code shows
%let type2 = %scan((&typ_list.),&i.,',');
and so when you put parentheses around &typ_list, this is just adding the text ( at the left and adding the text ) at the right of &typ_list, which then gives ("Hybrid","SUV","Sedan","Sports","Truck","Wagon") as the argument to the %SCAN function. So the %SCAN function finds the first text string as ("Hybrid"
I think what you want is this:
%let type2 = %scan(%bquote(&typ_list.),&i.,',');
The %quote masks the commas inside &typ_list so they are not used as a separator of different arguments to %SCAN, and the parentheses in %BQUOTE( ) are not interpreted as being added to the text string.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.