topic Re: Help with a macro that removes a certain prefix from all variables in a table in SAS Programming

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

Sarah_Caro — Mon, 30 Oct 2023 13:45:51 GMT

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?

Re: Help with a macro that removes a certain prefix from all variables in a table

PaigeMiller — Mon, 30 Oct 2023 13:51:18 GMT

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

Re: Help with a macro that removes a certain prefix from all variables in a table

Sarah_Caro — Mon, 30 Oct 2023 14:21:58 GMT

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.

Re: Help with a macro that removes a certain prefix from all variables in a table

maguiremq — Mon, 30 Oct 2023 14:24:19 GMT

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

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

Re: Help with a macro that removes a certain prefix from all variables in a table

PaigeMiller — Mon, 30 Oct 2023 14:27:53 GMT

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

Re: Help with a macro that removes a certain prefix from all variables in a table

maguiremq — Mon, 30 Oct 2023 14:25:18 GMT

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

Re: Help with a macro that removes a certain prefix from all variables in a table

Sarah_Caro — Mon, 30 Oct 2023 14:28:21 GMT

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.

Re: Help with a macro that removes a certain prefix from all variables in a table

PaigeMiller — Mon, 30 Oct 2023 14:30:46 GMT

Your data set name must be in CAPITAL letters

Re: Help with a macro that removes a certain prefix from all variables in a table

Sarah_Caro — Mon, 30 Oct 2023 14:32:25 GMT

Success! Thanks a ton!

Re: Help with a macro that removes a certain prefix from all variables in a table

Sarah_Caro — Mon, 30 Oct 2023 14:35:53 GMT

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?

Re: Help with a macro that removes a certain prefix from all variables in a table

PaigeMiller — Mon, 30 Oct 2023 14:38:03 GMT

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.

Re: Help with a macro that removes a certain prefix from all variables in a table

Sarah_Caro — Mon, 30 Oct 2023 14:41:13 GMT

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

Re: Help with a macro that removes a certain prefix from all variables in a table

Quentin — Mon, 30 Oct 2023 15:09:10 GMT

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.

Re: Help with a macro that removes a certain prefix from all variables in a table

Sarah_Caro — Mon, 30 Oct 2023 15:12:25 GMT

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.