Macro-one/two explanatory variables

[Kurt_Bremser]

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

[tarheel13]

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

[Ronein]

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**/

[Kurt_Bremser]

I moved this back in here, as I also provided the answer here.

[Ronein]

Thanks,

Your code solved the problem but now there is a new problem.

When I provide 2 variables then I get an error.

Spoiler

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)


 
 

[Kurt_Bremser]

Compare my PUT line closely with yours, you'll find the mistake.