CODEWITHSUNDEEP
sassas-macro
KDPinCA
KDPinCA

Reputation: 65

Calling a macro variable from libname

How do I call a macro variable in the from clause of proc sql if I wish to use it in a libname? Let me show you what I mean:

options nofmterr;
libname FRST "/ecm/retail/mortgage/nbk6kra/LGD/data/frst_201312bkts";
libname HELN "/ecm/retail/mortgage/nbk6kra/LGD/data/heln_201312bkts";
libname HELC "/ecm/retail/mortgage/nbk6kra/LGD/data/helc_201312bkts";

%let pathLGD = /new/mortgage/2014Q4/LGD;
%let prod = FRST; 

/**************** Segment calculation **************** Date filter to be consistent with model documentation for segmented tables****************/

%macro Performance(prod);

proc sql;
create table lgd_seg_&prod as 
select distinct
SegDT_LGD_2013,
min(ScoreDT_LGD_2013) as min_range,
max(ScoreDT_LGD_2013) as max_range,
count(*) as count,
mean(lgd_ncl_adjusted) as LGD_actual, 
mean(ScorePIT_LGD_2013) as LGD_pred_pit_1, 
mean(ScoreDT_LGD_2013) as LGD_pred_dt_1
from "&prod."scored;
where coh_asof_yyyymm > 200612
group by 1;
quit;

PROC EXPORT DATA=lgd_seg_&prod._fs
                OUTFILE= "&pathLGD./lgd_seg.xlsx" 
                DBMS=XLSX REPLACE;
         SHEET="&prod._lgd_seg_fs"; 
    RUN;

%mend;

%Performance(prod=FRST);        
%Performance(prod=HELN);        
%Performance(prod=HELC);

So in the "from" clause, the macro is supposed to read FRST.scored, HELN.scored, and HELC.scored respectively. Currently it cannot find the file, and if I were to remove the quotation marks, then it'd become "work.FRSTscored". I hope I've made this clear. Any input and comment is appreciated.

Upvotes: 1

Views: 1058

Answers (2)

Harshad Patil
Harshad Patil

Reputation: 313

Remove quotation marks applied outside the macro variable prod and use two dots after macro variable (one to signify end of macro variable name and second one to specify the table name after the libname reference.

from &prod..scored

Upvotes: 0

SRSwift
SRSwift

Reputation: 1710

When you want to follow the the resolved value of a macro variable with an immediate additional character you should escape the macro variable with a full stop (.). For example:

%let start = one;
%put &start.two;
%put &start..two;
%put &startend;

onetwo
one.two
WARNING: Apparent symbolic reference STARTEND not resolved.

So your code should read from &prod..scored;.

If you ever need to you can also delay the resolution of a macro variable with double ampersand (&&):

%let end = two;
%let onetwo = three;
%put &&one&end;
%put &&&start&end;

Three
Three

Or:

%let three = inception;
%put &&&&&&&start&end;

inception

Upvotes: 2

Related Questions