topic Re: Proc SQL question in New SAS User

Proc SQL question

brainupgraded — Thu, 06 Jun 2019 17:04:36 GMT

I am trying to add a suffix to all variables, but below codes give me an error message. The message says,

"NOTE 137-205: Line generated by the invoked macro "SUFFIXTOALLVAR".
2 quit; data &ndsn;set &lib..&dsn (rename=(&list)); run;
-
23
ERROR 23-7: Invalid value for the RENAME option."

I am not much familiar with SQL, so I would really appreciate it if anybody can help.

%macro suffixtoallvar(lib, dsn, suffix, ndsn);
	proc contents data=&lib..&dsn noprint;
	run;
	proc sql noprint;
		select cats(name,'=',name,'_&suffix')
		into :list
		separated by ' '
		from dictionary.columns
			where libname='WORK' and memname='&ndsn';
	quit;
	data &ndsn;set &lib..&dsn (rename=(&list));
	run;
%mend suffixtoallvar;

%suffixtoallvar(MM18, RES_WK_MUSICEVENT, me, res_wk_me);

Re: Proc SQL question

Tom — Thu, 06 Jun 2019 17:15:39 GMT

The macro processor ignores text inside of single quote characters. Use double quote characters instead.

cats(name,'=',name,"_&suffix")

Re: Proc SQL question

brainupgraded — Thu, 06 Jun 2019 17:18:36 GMT

Thanks! But I still get the same error message. It seems that it's about the rename statement in the last datastep: "&dsn (rename=(&list));" "&" before list seems to be the problem according to SAS log. Any solution?

Re: Proc SQL question

Tom — Thu, 06 Jun 2019 18:01:04 GMT

@brainupgraded wrote:

Thanks! But I still get the same error message. It seems that it's about the rename statement in the last datastep: "&dsn (rename=(&list));" "&" before list seems to be the problem according to SAS log. Any solution?

It would only be generating an error complaining about &LIST if the macro variable LIST did not get defined.

That could happen with your query since the INTO clause of a SELECT statement will NOT create the macro variable if the query doesn't find any matching records.

The fix for that is to set a value for list BEFORE the query.

%let list=;

Note that your query should always not find any matching records. You probably do not have any datasets named '&ndsn' since & is not normally a valid character to use in a dataset name. Make sure to use double quotes when want the macro processor to resolve text inside of quotes.

Re: Proc SQL question

Tom — Thu, 06 Jun 2019 18:03:12 GMT

Why are you running PROC CONTENTS without either printing the result or using the OUT= option to make a dataset?

Why are you (once you fix the quotes) asking for variable names from the TARGET dataset instead of the SOURCE dataset?

Re: Proc SQL question

brainupgraded — Thu, 06 Jun 2019 18:12:14 GMT

I am trying to write a macro for this: http://support.sas.com/kb/48/674.html.

I am doing it because I have multiple datasets I want to run with this renaming macro.

The only reason I run Proc Contents with noprint option is that somebody suggested it on another webpage. Does it affect dictionary.columns perhaps?

I just found a macro called "ren_stem" and it works great. But I just want to understand which parts I am wrong about in my codes.

I tried your suggestion about "%let list=" but I am not sure what to put on the right side of the equal sign. I would really appreciate it if you can correct my codes.

Thanks!

Re: Proc SQL question

Tom — Thu, 06 Jun 2019 18:22:19 GMT

Before you can create a macro get the code working first.

You can use EITHER proc contents OR dictionary.columns. No need to use both.

PROC CONTENTS is much easier to understand and debug. Plus if you have a lot of open librefs it can be a lot faster.

So first get this to run.

proc contents data=mm18.res_wk_musicevent noprint out=_contents;
run;

proc sql noprint;
%let list=;
select cats(name,'=',name,'_me')
  into :list separated by ' '
  from _contents 
;
quit;

data res_wk_me;
  set mm18.res_wk_musicevent;
  rename &list;
run;

If that works then start replacing the variable parts with macro variable references. Watch out when using string literals that you use appropriate outer quotes.

%let in=mm18.res_wk_musicevent;
%let suffix=me;
%let out=res_wk_me;

proc contents data=&in. noprint out=_contents;
run;

proc sql noprint;
%let list=;
select cats(name,'=',name,'_',"&suffix.")
  into :list separated by ' '
  from _contents 
;
quit;

data &out.;
  set &in.;
  rename &list;
run;

See if that works.

Then convert it into a macro.

%macro rename(in,suffix,out);
proc contents data=&in. noprint out=_contents;
run;

proc sql noprint;
%let list=;
select cats(name,'=',name,'_',"&suffix.")
  into :list separated by ' '
  from _contents 
;
quit;

data &out.;
  set &in.;
  rename &list;
run;
%mend rename;

And try calling the macro.

%rename(in=mm18.res_wk_musicevent,suffix=me,out=res_wk_me);

Re: Proc SQL question

brainupgraded — Thu, 06 Jun 2019 18:31:47 GMT

Everything works perfectly. Thank you so much for your help! 🙂

Re: Proc SQL question

ballardw — Thu, 06 Jun 2019 20:27:09 GMT

@brainupgraded wrote:

I am trying to add a suffix to all variables, but below codes give me an error message. The message says,

"NOTE 137-205: Line generated by the invoked macro "SUFFIXTOALLVAR".
2 quit; data &ndsn;set &lib..&dsn (rename=(&list)); run;
-
23
ERROR 23-7: Invalid value for the RENAME option."

I am not much familiar with SQL, so I would really appreciate it if anybody can help.
%macro suffixtoallvar(lib, dsn, suffix, ndsn);
	proc contents data=&lib..&dsn noprint;
	run;
	proc sql noprint;
		select cats(name,'=',name,'_&suffix')
		into :list
		separated by ' '
		from dictionary.columns
			where libname='WORK' and memname='&ndsn';
	quit;
	data &ndsn;set &lib..&dsn (rename=(&list));
	run;
%mend suffixtoallvar;

%suffixtoallvar(MM18, RES_WK_MUSICEVENT, me, res_wk_me);

In addition to the macro variable and quotes issue, the DICTIONARY tables will want the MEMNAME to also be all capitals as you show WORK since that is how the data set names are stored. So "res_wk_me" would not be in the dictionary table so the list would be empty because a matching member name wasn't found.