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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.