Help with a macro that removes a certain prefix from all variable names in a table

[Sarah_Caro]

Hi all,

I am in base SAS, and I would like to replace the prefix from the variable name of all variables in a dataset. For example, I have a dataset with 125 columns that all begin with "SGBSTDN_", and I want to remove the "SGBSTDN_" from the variable name for each of the 125 columns. I have written the following macro code to accomplish this, but when I try to call the macro, I get an error (screenshot below). Here is my code:

%macro rmv_pfx(prefix, dataset);
/* Get the list of variables in the dataset */
%let dsid = %sysfunc(open(&dataset));
%let num_vars = %sysfunc(attrn(&dsid, nvars));
%do i = 1 %to &num_vars;
%let var_name = %sysfunc(varname(&dsid, &i));
%let new_var_name = %scan(&var_name, -1, &prefix); 

/* Rename the variable */
rename &var_name = &new_var_name;
%end;
%let dsid = %sysfunc(close(&dsid));
%mend;

And, here is what I am typing to call the macro. The name of my dataset is "d".
%rmv_pfx(SGBSTDN_, d);

Can anyone help let me know why SAS is throwing the error in the screenshot?

 

[PaigeMiller]

No macros needed.

/* UNTESTED CODE */
proc sql noprint;
    select cats(name,'=',substr(name,9)) into :renames separated by ' ' from dictionary.columns where 
    upcase(name) eqt 'SGBSTDN_' and memname='YOURDATASETNAME' and library='WORK';
quit;

proc datasets library=work nolist;
    modify yourdatasetname;
    rename &renames;
run; quit;

I believe your code fails because the RENAME statement must be in a SAS data step or PROC DATASETS. In the future, do NOT show us tiny portions of the LOG, we needed to see the ENTIRE log (not just the errors). 

[Sarah_Caro]

Thank you for this. I believe I typed your code exactly (except I replaced "your dataset" with the name of my dataset). Unfortunately I got an error, which I'll paste the screenshot below.

I can't copy the text (either into or out of SAS) since I'm working in SAS on a secured desktop environment without internet, but I can show a screenshot. This is the log with the error codes:

 Any ideas? Many thanks.

[maguiremq]

@PaigeMiller's solution was untested, so this was expected.

Use LIBNAME = "WORK" instead. Always use caps when you use dictionary.tables/columns.

[PaigeMiller · Accepted solution]

Sorry about that. I made a mistake, it should be

    upcase(name) eqt 'SGBSTDN_' and memname='YOURDATASETNAME' and libname='WORK';

and yes, the data set name and libname must be in capital letters 

[maguiremq]

I would recommend marking @PaigeMiller's response as the correct one.

[Sarah_Caro]

In my haste I accidentally accepted the one where you corrected libname, instead of @PaigeMiller ...

I don't suppose there's a way to undo this? 

[PaigeMiller]

I believe that if you scroll to the message you marked correct, you can un-mark it as correct via a button that appears there.

[Sarah_Caro]

Yes, I did at least think of that 😉 but there is no button to undo it. Here is how it appears.  Sorry about that!! 

[Quentin]

I think if you click the 'hamburger' button (three lines) under the photo, there may be an option in that menu to unmark as correct.  But it may not be there, depending on your rank, and amount of time since you marked it correct.

[Sarah_Caro]

Great, thanks! I was able to do it and then marked PaigeMiller's post (the later one where he corrected the code) as the solution. I hope that is the convention here - I didn't mark the first one with the full code, because it contained a slight error. 

Thanks again to all.

[Sarah_Caro]

Thank you, that did resolve the first error. The only thing that seems to be sticking is it does not seem to like the line

rename &renames;

I have pasted the new log below. Thanks again.

 

[PaigeMiller]

Your data set name must be in CAPITAL letters

[Sarah_Caro]

Success! Thanks a ton!