topic Re: Setting Libname for macro in SAS Programming

Setting Libname for macro

ryyno10 — Sat, 09 Nov 2019 12:28:25 GMT

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!

Re: Setting Libname for macro

PaigeMiller — Sat, 09 Nov 2019 13:11:55 GMT

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.

Re: Setting Libname for macro

VDD — Sat, 09 Nov 2019 13:52:24 GMT

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

Re: Setting Libname for macro

Tom — Sat, 09 Nov 2019 16:03:20 GMT

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.

Re: Setting Libname for macro

ryyno10 — Mon, 11 Nov 2019 14:08:59 GMT

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

Re: Setting Libname for macro

ryyno10 — Mon, 11 Nov 2019 14:09:33 GMT

Already did this

Re: Setting Libname for macro

ryyno10 — Mon, 11 Nov 2019 14:41:47 GMT

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;

Re: Setting Libname for macro

ryyno10 — Mon, 11 Nov 2019 14:51:39 GMT

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

Re: Setting Libname for macro

Tom — Mon, 11 Nov 2019 15:01:27 GMT

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

Re: Setting Libname for macro

ryyno10 — Mon, 11 Nov 2019 15:26:22 GMT

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.

Re: Setting Libname for macro

Tom — Mon, 11 Nov 2019 15:35:00 GMT

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;