CODEWITHSUNDEEP
sas
duckman
duckman

Reputation: 747

SAS: create a Macro that add suffix to variables in a dataset

I would like to create a macro that add a suffix to variable names in a dataset. below is my code:

%macro add_suffix(library=,dataset=,suffix=);
    proc sql noprint;
        select cat(name, ' = ', cats('&suffix.',name )) into :rename_list separated by ' ' from
        dictionary.columns where libname = '&library.' and memname= '&dataset.';
    quit;

    proc datasets library=&library nolist nodetails;
        modify &dataset;
        rename &rename_list;
    run;

    quit;

%mend;
%add_suffix(library=OUTPUT,dataset=CA_SPREADS,suffix=CA);

It gives error messages: 

NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


WARNING: Apparent symbolic reference RENAME_LIST not resolved.
NOTE: Line generated by the invoked macro "ADD_SUFFIX".
2                                                 rename &rename_list;     run;
                                                         -
                                                         22
                                                         76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

If I put the library and dataset names in quotation mark, it works for the first block i.e. add values to string rename_list but not for the proc dataset step

Upvotes: 1

Views: 6226

Answers (3)

pinegulf
pinegulf

Reputation: 1396

Tested and works. Longer but maybe more beginner friendly approach. Input the dataset name and what suffix you want to add.

example: %add_suffix(orders, _old); /* will add _old suffix to all variables.*/

%macro Add_Suffix(Dataset, suffix);
    proc contents noprint
        data=work.&dataset out=sjm_tmp(keep=NAME);
    run;

    data sjm_tmp2;
        set sjm_tmp;
        foobar=cats(name, '=',NAME,'&suffix.');
    run;

    proc sql noprint;
        select foobar into :sjm_list separated by ' ' from sjm_tmp2;
    quit;

    proc datasets library = work nolist;
       modify &dataset;
       rename &sjm_list;
    quit;

    proc datasets library=work noprint;  
       delete sjm_tmp sjm_tmp2 ;  
    run;
%mend Add_Suffix;

Upvotes: 1

Tom
Tom

Reputation: 51566

Macro triggers like % and & are not honored inside of single quotes. That is why you are not getting any hits on your SQL query. There is no library name that has an & as the first character.

The reason it looked like it was sort of working is that when you use this in your SQL statement

catx('=',name,cats('&prefix.',name))

then you end up with a string like

age=&prefix.age

And that will actually work because the reference to the macro variable PREFIX will resolve when you run the RENAME statement.

You should just use double quotes instead.

%macro change_names(library=,dataset=,prefix=,suffix=);
%local rename_list;
proc sql noprint;
  select catx('=',name,cats("&prefix",name,"&suffix"))
    into :rename_list separated by ' ' 
  from dictionary.columns
  where libname = %upcase("&library")
    and memname = %upcase("&dataset")
  ;
quit;

%if (&sqlobs) %then %do;
proc datasets library=&library nolist nodetails;
  modify &dataset;
    rename &rename_list;
  run;
quit;
%end;
%else %put WARNING: Did not find any variables for &library..&dataset..;
%mend change_names;

%change_names(library=OUTPUT,dataset=CA_SPREADS,prefix=CA);

Upvotes: 3

user667489
user667489

Reputation: 9569

  1. Your macro variables are not being resolved because you're wrapping them in single quotes ' rather than double quotes ".
  2. You should uppercase the libname and memname parameters of your macro as these are always in uppercase in dictionary.columns.

Upvotes: 1

Related Questions