topic Re: rename variables automatically before merge datasets in SAS Programming

rename variables automatically before merge datasets

Fersal — Fri, 08 Apr 2016 12:16:05 GMT

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

Re: rename variables automatically before merge datasets

Loko — Fri, 08 Apr 2016 12:21:47 GMT

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;

Re: rename variables automatically before merge datasets

RW9 — Fri, 08 Apr 2016 12:42:59 GMT

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;

Re: rename variables automatically before merge datasets

Tom — Fri, 08 Apr 2016 13:39:36 GMT

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

Re: rename variables automatically before merge datasets

Fersal — Fri, 08 Apr 2016 15:22:03 GMT

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

Re: rename variables automatically before merge datasets

Fersal — Fri, 08 Apr 2016 15:23:50 GMT

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.

Re: rename variables automatically before merge datasets

ballardw — Fri, 08 Apr 2016 15:42:30 GMT

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.

Re: rename variables automatically before merge datasets

Fersal — Fri, 08 Apr 2016 17:18:49 GMT

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

Re: rename variables automatically before merge datasets

RW9 — Mon, 11 Apr 2016 09:24:56 GMT

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.