We need to prevent the %SCAN function from using the dollar sign as a delimiter, so we need to add a blank:
put(%scan(&list1.,&j.),%scan(&list2.,&j.,%str( )).) as %scan(&list1.,&j.),
I think this should work
%macro BBB(sourceTbl=,TargetTbl=,list1=,list2=);
%let nr_vars=%sysfunc(countw(&list1.));
%put &nr_vars.;
proc sql;
create table &TargetTbl. as
select
%do j = 1 %to &nr_vars.;
put(%scan(&list1.,&j.),%scan(&list2.,&j.).) as %scan(&list1.,&j.)_f,
%end;
count(*) as nr format=comma21.,
calculated nr/(select count(*) as total from &sourceTbl.) as PCT format=percent8.1
from &sourceTbl.
%if &nr_vars. > 0
%then %do;
group by
%do j = 1 %to &nr_vars.;
%if &j. > 1 %then ,;
%scan(&list1.,&j.)_f
%end;
%end;
;
select cats(name,'=',transtrn(name,'_f',trimn('')))
into: suffixlist separated by ' '
from dictionary.columns
where libname='WORK' and upper(memname)=%upcase("&targetTbl") and 'F'=scan(upcase(name),-1,'_');
quit;
%put &=suffixlist;
data &targetTbl;
set &targetTbl;
rename &suffixlist;
run;
%mend BBB;
proc format;
value Cylinder_fmt
.='Unknown'
1-4='1-4'
5-high='5+'
;
Run;
%BBB(sourceTbl=sashelp.cars,
TargetTbl=wanted,
list1= Cylinders MPG_City,
list2=Cylinder_fmt best.)
Hello
What is the way to solve the error please?
I dont want to change the "PUT" in the macro.
/*ERROR: Numeric format F in PUT function requires a numeric argument**/
%macro BBB(sourceTbl,TargetTbl,list1=,list2=);
%let nr_vars=%sysfunc(countw(&list1.));
%put &nr_vars.;
proc sql;
create table &TargetTbl. as
select
%do j = 1 %to &nr_vars.;
put(%scan(&list1.,&j.),%scan(&list2.,&j.).) as %scan(&list1.,&j.),
%end;
count(*) as nr format=comma21.,
calculated nr/(select count(*) as total from &sourceTbl.) as PCT format=percent8.1
from &sourceTbl.
%if &nr_vars. > 0
%then %do;
group by
%do j = 1 %to &nr_vars.;
%if &j. > 1 %then ,;
calculated %scan(&list1.,&j.)
%end;
%end;
;
quit;
%mend ;
/*Distribution by field Origin-Get error Here***/
/*Distribution by field Origin-Get error Here***/
/*Distribution by field Origin-Get error Here***/
%BBB(sourceTbl=sashelp.cars,
TargetTbl=wanted,
list1=Origin,
list2=$32)
/*ERROR: Numeric format F in PUT function requires a numeric argument**/
I moved this back in here, as I also provided the answer here.
Thanks,
Your code solved the problem but now there is a new problem.
When I provide 2 variables then I get an error.
NOTE: Line generated by the macro function "SCAN". 30 best best ____ 22 202 NOTE 137-205: Line generated by the invoked macro "BBB". 30 put(%scan(&list1.,&j.),%scan(&list2.,&j.,).) as %scan(&list1.,&j.), _ 22 ERROR 22-322: Syntax error, expecting one of the following: a format name, ?. ERROR 202-322: The option or parameter is not recognized and will be ignored. NOTE: Line generated by the invoked macro "BBB". 30 put(%scan(&list1.,&j.),%scan(&list2.,&j.,).) as %scan(&list1.,&j.), _ 76 ERROR 76-322: Syntax error, statement will be ignored.
%macro BBB(sourceTbl,TargetTbl,list1=,list2=);
%let nr_vars=%sysfunc(countw(&list1.));
%put &nr_vars.;
proc sql;
create table &TargetTbl. as
select
%do j = 1 %to &nr_vars.;
put(%scan(&list1.,&j.),%scan(&list2.,&j.,%str()).) as %scan(&list1.,&j.),
/* put(%scan(&list1.,&j.),%scan(&list2.,&j.).) as %scan(&list1.,&j.),*/
%end;
count(*) as nr format=comma21.,
calculated nr/(select count(*) as total from &sourceTbl.) as PCT format=percent8.1
from &sourceTbl.
%if &nr_vars. > 0
%then %do;
group by
%do j = 1 %to &nr_vars.;
%if &j. > 1 %then ,;
calculated %scan(&list1.,&j.)
%end;
%end;
;
quit;
%mend ;
/**Error!!!***/
%BBB(sourceTbl=sashelp.cars,
TargetTbl=wanted1,
list1=Cylinders EngineSize,
list2=best best)
/**Working well**/=
%BBB(sourceTbl=sashelp.cars,
TargetTbl=wanted2,
list1=Origin,
list2=$32)
/**Working well**/
%BBB(sourceTbl=sashelp.cars,
TargetTbl=wanted3,
list1=Cylinders,
list2=best)
Compare my PUT line closely with yours, you'll find the mistake.
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.