Setting Libname for macro

[ryyno10]

I'm trying to set a libname step with a macro, which looks like this:

%include "Z:\somelocation\somemacro.sas";

%merge (name= varname, file= filename);

run;

endsas;

[include macro: somemacro.sas] 

options ps=max ls=232 nocenter nodate;

%macro merge (name = , file = );

libname in1 ("Z:\date1\sasdata",

                     "Z:\date2\sasdata");

When running the program, in1 assigns appropriately but the macro variables don't "resolve":

"WARNING:  Apparent symbolic reference FILE not resolved"

If I place the "in2" outside of the macro, the macro resolves but the "in2" library reference does not resolve. I've created several files using similar techniques (without error); it seems I'm missing a simple detail and would greatly appreciate any help.

Thanks!

[PaigeMiller · Accepted solution]

As the first line of your program, use

options mprint;

Then run the program again, and paste the ENTIRE log (not just the error messages) as text (not a screen capture) in your reply by clicking on the {i} icon and pasting the log into the window that appears. Do not skip this step.

[ryyno10]

Thanks Paige, I am running the program right now. Will post the results when it finishes. 

[ryyno10]

Here is the log. I had to make some modifications for privacy concerns. Thanks again for your help


The merge is in progress right now and the data but I peeked at the log file:
    
1          options ls=232 ps=max nocenter nodate mprint;
2          /******************************************************************/
3          /*                                                                */
4          /******************************************************************/
5          %include "Z:\sasmacro.pgms\prst.24maftclm.betosmerge.macro.sas";
69         
70            /************************/
71            /* prostate cancer      */
72            /************************/
73         %merge (cncr = prst, file = nch);
MPRINT(MERGE):   libname in1 ("Z:\dx2009\sasdata", "Z:\dx2010\sasdata", "Z:\dx2011\sasdata", "Z:\dx2012\sasdata", 
"Z:\dx2013\sasdata");
NOTE: Libref IN1 was successfully assigned as follows: 
      Levels:           5
      Engine(1):        V9 
      Physical Name(1): Z:\dx2009\sasdata
      Engine(2):        V9 
      Physical Name(2): Z:\dx2010\sasdata
      Engine(3):        V9 
      Physical Name(3): Z:\dx2011\sasdata
      Engine(4):        V9 
      Physical Name(4): Z:\dx2012\sasdata
      Engine(5):        V9 
      Physical Name(5): Z:\dx2013\sasdata
MPRINT(MERGE):   data clms;
MPRINT(MERGE):   set in1.prst_24maft_dx2009 in1.prst_24maft_dx2010 in1.prst_24maft_dx2011 in1.prst_24maft_dx2012;
 
NOTE: There were 390000 observations read from the data set IN1.PRST_24MAFT_DX2009.
NOTE: There were 3800000 observations read from the data set IN1.PRST_24MAFT_DX2010.
NOTE: There were 3900000 observations read from the data set IN1.PRST_24MAFT_DX2011.
NOTE: There were 3300000 observations read from the data set IN1.PRST_24MAFT_DX2012.
NOTE: The data set WORK.CLMS has 15000000 observations and 112 variables.
NOTE: DATA statement used (Total process time):
      real time           2:39.07
      cpu time            58.61 seconds
      
 
MPRINT(MERGE):   data bhcpcs;
MPRINT(MERGE):   set "Z:\sasdata\betoshcpcs";
 
NOTE: There were 16048 observations read from the data set Z:\sasdata\betoshcpcs.
NOTE: The data set WORK.BHCPCS has 16048 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.91 seconds
      cpu time            0.04 seconds

[VDD]

stop trying to start with macros and get the process to work first.

[ryyno10]

Already did this

[Tom]

You have not really provided enough information to understand what code you are actually running.

But most likely the error about macro variables not resolving is caused by you trying to reference macro variables that are LOCAL to the macro after the macro has finished running.

For example that macro variable FILE in your %MERGE() macro is local (all macro parameters are local macro variables).  So you cannot do something like:

%merge(file=FRED);
libname in2 "&file";

And expect IN2 to point to the 'FRED' directory.  If FILE existed before the call to %MERGE() then it will still have the same value after %MERGE() ends, it will NOT have been change to FRED. If there was no FILE macro variable then SAS will say it cannot find it and try to use the literal text '&file' as the path for the libname statement.

[ryyno10]

Hi Tom thanks for your advice. I had the following code:

%merge (cncr = prst, file = nch); 

%macro merge (cncr=, file = );

libname in1 ("Z:\dx2009\sasdata",
                     "Z:\dx2010\sasdata",
                     "Z:\dx2011\sasdata");

data clms; 
  set in1.&cncr_&file.clm_dx2009; 
        in1.&cncr_&file.clm_dx2010; 
        in1.&cncr_&file.clm_dx2011; 

data bhcpcs; 
  set "Z:\sasdata\betoshcpcs";

proc sql noprint;
create table clm24maftbts as 
select * 
from clms as A
left join bhcpcs as B
on a.hcpcs = b.hcpcs
order by patient_id;
quit;

data "Z:\sasdata\&cncr.clm24maftbts_dx2009_2011";
  set clm24maftbts;

%mend merge;

[Tom]

You have the CALL to the macro before the DEFINITION of the macro, but let's just assume that was just a mistake in posting here.

Let's do a quick review of the macro:

• You are telling SAS where one of your macro variable names ends, but not the other.  So you are referencing a macro variable named CNCR_ that you have not defined instead of the CNCR parameter.
• You appear to be putting semi-colons in the middle of your SET statement.  To avoid this I recommend placing the ending semi-colon for multiple line statements on a new line instead of hiding it at the end of the last line.  This coding pattern will make it easier for you to notice this type of mistake.
• You have left the last data step open.  Are you intending to add more statements to that last data step after the call to the macro? If not then include the RUN: statement in the macro definition.
• You are using two different methods to address SAS datasets.  Sometimes you are defining a libref and using two level names and other times you are just using a quoted physical name.  Is there any reason you need to use both methods instead of just standardizing on one?

%macro merge (cncr=, file= );

data clms; 
  set "Z:\dx2009\sasdata\&cncr._&file.clm_dx2009"
      "Z:\dx2010\sasdata\&cncr._&file.clm_dx2010"
      "Z:\dx2011\sasdata\&cncr._&file.clm_dx2011"
  ;
run;

proc sql noprint;
create table "Z:\sasdata\&cncr.clm24maftbts_dx2009_2011" as 
  select * 
  from clms as A
    left join "Z:\sasdata\betoshcpcs" as B
      on a.hcpcs = b.hcpcs
  order by patient_id
;
quit;

%mend merge;

%merge (cncr = prst, file = nch); 

[ryyno10]

Sorry for the confusion Tom.
(I did invert the macro/merge steps)
1) In modifying the code for privacy reasons, I also clipped the periods at the end of the var &cncr.
2) Yes, good advice
3) Yes, just a proc freq statement at the end of the program.
4) There is some inconsistency as I was trying to determine the source of the error.

[Tom]

SAS can confuse itself when you are trying to build one SAS "word" from multiple macro variables into thinking you have two words , or tokens, especially when you do it inside of a macro.

Using the quoted physical names will help since the quoted string is passed by the macro processor onto SAS after all of the macro triggers are resolved.

Otherwise I find that it just helps to build up complex dataset names, like in your example, into a new macro variable and then use that.

For example you might want to introduce a %DO loop to generate the year by year dataset names.

%macro xxx ....
%local year memname ;
...
SET
%do year = 2009 %to 2011 ;
  %let memname=&cncr._&file.clm_dx&year. ;
  in1.&memname
%end;
;
...
%mend;