Reading a character string into new column from macro variable

[GuyTreepwood]

Hello,

I am trying to run the following macro code...

%macro doranks(types=,models=,list=);
  %do t=1 %to %sysfunc(countw(&types));
    %let type=%scan(&types,&t);
    %do m=1 %to %sysfunc(countw(&models,%str( ),q));
	  %let model=%scan(&models,&m,%str( ),q));
      %do L=1 %to %sysfunc(countw(&list));
        %let listitem=%scan(&list,&L);	
		
		proc sql;
		create table Top5_Rank_&type._&listitem (drop=Rank) as select  
                Rank,
		&model as Model,
		sum(target)  as tot_target,
		calculated tot_pct_target/1000 as pct_all_target format percent8.1 

		from ranked_&type._&listitem

		where Rank = 1

		group by Rank;
		quit;
	  %end;
    %end;
  %end;
%mend; 

%doranks(types=DT LR RF, models='Decision Tree' 'Logistic Regression' 'Random_Forest',  list=w_Enc wo_Enc);

..and I keep getting an error regarding the '&model' macro variable. 

It seems like the code is adding a parenthesis at the end of the character value that I am trying to assign to the new column. Here is the part of the error log message that shows this:

NOTE: Line generated by the macro variable "MODEL".

130 'Decision Tree')

-

22

200

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,

AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,

LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

I would like the code to assign the values found in the 'models' list in the %doranks macro statement into the new 'Model' column created in the proc sql code.

[Reeza]

    %do m=1 %to %sysfunc(countw(&models,%str( ),q));
	  %let model=%scan(&models,&m,%str( ),q));

This is using a space as the delimiter for you macro variable list. Since you have two words in model, the delimiter isn't working correctly. Instead, use a a different delimiter for this list and modify the macro code accordingly.

    %do m=1 %to %sysfunc(countw(&models, |,q));
	  %let model=%scan(&models,&m,|,q));

 And:

%doranks(types=DT LR RF, models='Decision Tree' |'Logistic Regression' |'Random_Forest',  list=w_Enc wo_Enc);

[Tom]

It is, because you have an extra ) in the %SCAN() function call.  Perhaps you copied the %SYSFUNC(COUNTW()) code and forgot to remove the extra one.

let model=%scan(&models,&m,%str( ),q);

[GuyTreepwood]

Your suggestion did get the code run successfully without any errors. However, I am now running into the issue where the code is only reading the last character string ('Random Forest') into every dataset created by the proc sql code block. I tried adding the pipe delimiters as suggested by @Reeza, but the code is still returning the same value in every dataset created. 

[Tom]

Looks to me like the MODEL list is just a set of labels for the TYPE list.  So you have one two many %DO loops.

%macro doranks(types=,models=,list=);
%local nt t type nm model l listitem inds outds ;
%let nt=%sysfunc(countw(&types,%str( )));
%let nm=%sysfunc(countw(&models,%str( ),q));
%if &nt ne &nm %then %do;
  %put ERROR: Unequal number of types and models ;
%end;
%else %do t=1 %to &nt;
  %let type=%scan(&types,&t);
  %let model=%scan(&models,&t,%str( ),q);
  %do L=1 %to %sysfunc(countw(&list,%str( )));
    %let listitem=%scan(&list,&L,%str( ));	
    %let outds = Top5_Rank_&type._&listitem ;
    %let inds = ranked_&type._&listitem ;

proc sql;
  create table &outds (drop=Rank) as select  
     Rank
    ,&model as Model
    ,sum(target) as tot_target
    ,calculated tot_pct_target/1000 as pct_all_target format=percent8.1 
  from &inds
  where Rank = 1
  group by Rank
  ;
quit;
  %end;
%end;
%mend;

[ErikLund_Jensen]

Hi @GuyTreepwood 

Your logic is basically working, but there are several issues with the actual code, both with syntax and using the correct index variables.

The following code has these issues solved, see comments. There is no input data provided, so the example just outputs the 3 x 2 x 3 set of macro variables for each iteration, but it should work with the Proc SQL step activated. 

%macro doranks(types=,models=,list=);

  * space works as delimiter between types;
  %do t=1 %to %sysfunc(countw(&types));
    %let type=%scan(&types,&t);

    * # used as delimiter between models because of embedded blanks;
    %do m=1 %to %sysfunc(countw(&models,#));

      %let model=%scan(&models,&m,#);
      * Insert _ instead of space im model name to make4 a valid variable name for proc sql;
      %let model = %sysfunc(translate(&model,_,%str( )));

      * space works as delimiter between list items;
      %do l=1 %to %sysfunc(countw(&list));

        %let listitem=%scan(&list,&l);	
        * Omit the sql step, just output the generated macro variables for the current iteration;

        %put &=type &=model &=listitem;
        /* 
        proc sql;
          create table Top5_Rank_&type._&listitem (drop=Rank) as select  
            Rank,
            &model as Model,
            sum(target)  as tot_target,
            calculated tot_pct_target/1000 as pct_all_target format percent8.1 
          from ranked_&type._&listitem
          where Rank = 1
          group by Rank;
        quit;
        */
      %end;
    %end;
  %end;
%mend; 

* # used as delimiter between models;
%doranks(types=DT LR RF, models='Decision Tree'#'Logistic Regression'#'Random_Forest', list=w_Enc wo_Enc);

The result is:

TYPE=DT MODEL='Decision_Tree' LISTITEM=w_Enc
TYPE=DT MODEL='Decision_Tree' LISTITEM=wo_Enc
TYPE=DT MODEL='Logistic_Regression' LISTITEM=w_Enc
TYPE=DT MODEL='Logistic_Regression' LISTITEM=wo_Enc
TYPE=DT MODEL='Random_Forest' LISTITEM=w_Enc
TYPE=DT MODEL='Random_Forest' LISTITEM=wo_Enc
TYPE=LR MODEL='Decision_Tree' LISTITEM=w_Enc
TYPE=LR MODEL='Decision_Tree' LISTITEM=wo_Enc
TYPE=LR MODEL='Logistic_Regression' LISTITEM=w_Enc
TYPE=LR MODEL='Logistic_Regression' LISTITEM=wo_Enc
TYPE=LR MODEL='Random_Forest' LISTITEM=w_Enc
TYPE=LR MODEL='Random_Forest' LISTITEM=wo_Enc
TYPE=RF MODEL='Decision_Tree' LISTITEM=w_Enc
TYPE=RF MODEL='Decision_Tree' LISTITEM=wo_Enc
TYPE=RF MODEL='Logistic_Regression' LISTITEM=w_Enc
TYPE=RF MODEL='Logistic_Regression' LISTITEM=wo_Enc
TYPE=RF MODEL='Random_Forest' LISTITEM=w_Enc
TYPE=RF MODEL='Random_Forest' LISTITEM=wo_Enc