BookmarkSubscribeRSS Feed
Fersal
Calcite | Level 5

Hi all,

 

I have made the following macro but I am getting a error message in the end, someone could help me to know what I'm doing wrong?

 

%MACRO RENAME (dataset1=, dataset2=);
PROC SQL;
DESCRIBE TABLE LeafL LeafW dictionary.columns;
QUIT;
PROC SQL;
SELECT libname, memname, name
FROM dictionary.columns
WHERE libname= "WORK" and memname= "&dataset1";
QUIT;
PROC SQL;
SELECT libname, memname, name
FROM dictionary.columns
WHERE libname= "WORK" and memname= "&dataset2";
QUIT;
PROC SQL;
SELECT trim(name) || "=" || "&dataset2" || "_" || name
INTO :renamevar separated by " "
FROM
(
SELECT name
FROM dictionary.columns
WHERE libname= "WORK" and memname= "&dataset2" and upcase(name) in
(
SELECT upcase(name)
FROM dictionary.columns
WHERE libname= "WORK" and memname= "&dataset1" and upcase(name) ne "PLOT"
)
);
QUIT
%PUT &renamevar;
DATA Land;
MERGE LeafL LeafW(rename=(&renamevar));
BY plot;
RUN;
%MEND;

%RENAME (dataset1= LeafL, dataset2= LeafW); RUN;

 

From the log windows (below) seem like there is something wrong with the macro variable but I don't know exactly what happen.

 

WARNING: Apparent symbolic reference RENAMEVAR not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the invoked macro "RENAME".
7 BY plot;
--
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

 

Thanks

8 REPLIES 8
Loko
Barite | Level 11

Hello,

 

The sql query which creates the macro variable renamevar returns no rows thus the message.

 

Consider the following example:

 

proc sql;
select age into :age from sashelp.class where age=678;
quit;

%put &age;

Fersal
Calcite | Level 5

but it works when it is out of the macro which I don't understand why...


What I did is add WHERE upcase(name) ne "PLOT" after the last query, now it works but I still get the following warning message

 

WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.

 

Thanks

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

To b e honest, I am quite suprised that a question like this comes up enough to warrant a macro for it?  I mean you must know what data you have coming in what data goes where etc.  In most case a small change to the structure of the data will eradicate the need for merging same data back to same data.  Could you give an exampe of where this would be needed?

As for the code, well you could do something like the below, but then you need to put in checks to see if there are any rows which have the same name element, data types etc.  It just seems like a large process with nothing to gain from it?

 

 

data tmp;
set sashelp.class (rename=(age=temp));
run;

proc sql noprint;
select cats(NAME,"=_",NAME)
into :VLIST separated by " "
from DICTIONARY.COLUMNS
where LIBNAME="WORK"
and MEMNAME="TMP"
and upcase(NAME) ne "NAME"
and NAME in (select NAME from DICTIONARY.COLUMNS where LIBNAME="SASHELP" and MEMNAME="CLASS");
quit;

data want;
merge sashelp.class tmp (rename=(&VLIST.));
by name;
run;

 

Fersal
Calcite | Level 5

Thanks for your comment, the reason I want to use a macro is because I have a lot of data set which I want to merge and the name of the most variables are the same but can be different values in each of them.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Yes, but my question is, what are these different variables, where do they come from, why do you not know what they are upfront?  It sounds like the process has broken further up the chain and now your trying to patch it together again.

Tom
Super User Tom
Super User

When you use the INTO clause on a SELECT statement to create a macro variable the variable is not created when there are no rows returned by the SELECT statement.  You just need to set the value BEFORE the select statement to prevent the error you are seeing.

 

PROC SQL;
%let renamevar=;
  SELECT trim(name) || "=" || "&dataset2" || "_" || name
    INTO :renamevar separated by " "
...
Fersal
Calcite | Level 5

Hi, I also have tried this, but it doesn't work

ballardw
Super User

One issue, the memname should be uppercase. Otherwise you won't get a match in the dictionary table.

 

This call to the macro at least is using mixed case.

%RENAME (dataset1= LeafL, dataset2= LeafW); RUN;

 

so either pass the parameter in upper case, %upcase the values at the top of the macro or use memname=upcase("&dataset1") in the SQL.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1938 views
  • 3 likes
  • 5 in conversation