Hi there
I am running into a problem where the macro variable XDataset is not being acknowledged.
I've tried a few ways to work it, but at its very basis, here is where it goes wrong
Data
BOTH is a dataset of names of SAS Datasets in a particular library (in2002 which is defined and ok)
I.e. (There are 24 of them which are being picked up ok)
Name
ADVERSE_SACQ |
CNTD_P_SACQ |
CONDRUG_SACQ |
CONTRT_SACQ |
DEMOG_SACQ |
FINAL_SACQ |
Why can't I access my in2002 file in my macro? Its absolutely there... I've seen that I should add it as a Global variable to my autoexec.sas but I don't have edit access to that file based on admin rights...
The program is:
%macro ChkCombine(ImportLibrary2=,ImportLibrary3=);
LIBNAME in1002 "C:\Users\<hidden>\SAS Files\B5201002_201709081643";
LIBNAME in1003 "C:\Users\<hidden>\SAS Files\B5201003_201710101208";
run;
proc sql;
select count(Name) into :NumOfDatasets from Both;
select Name into :XDataset1-:XDataset%trim(%left(&NumOfDatasets)) from Both;
quit;
%do index = 1 %to &NumOfDatasets;
%put index = &index;
%put NumofDatasets = &NumofDatasets;
%put XDataset1 = &XDataset1;
%put XDataset2 = &XDataset2;
%put XDataset24 = &XDataset24;
%put XDataset-index = &&XDataset&index
run;
Data Two;
set %unquote(&importlibrary2).%unquote(&XDataset&index);
run;
%end;
quit;
%mend;
%ChkCombine(ImportLibrary2=in2002,ImportLibrary3=in2003);
And here is the Log
397 %macro ChkCombine(ImportLibrary2=,ImportLibrary3=);
398 LIBNAME in1002 "C:\Users\<hidden>\SAS Files\B5201002_201709081643";
399 LIBNAME in1003 "C:\Users\<hidden>\SAS Files\B5201003_201710101208";
400 run;
401
402 proc sql;
403 select count(Name) into :NumOfDatasets from Both;
404 select Name into :XDataset1-:XDataset%trim(%left(&NumOfDatasets)) from Both;
405 quit;
406
407 %do index = 1 %to &NumOfDatasets;
408 %put index = &index;
409 %put NumofDatasets = &NumofDatasets;
410 %put XDataset1 = &XDataset1;
411 %put XDataset2 = &XDataset2;
412 %put XDataset24 = &XDataset24;
413 %put XDataset-index = &&XDataset&index
414 run;
415
416 Data Two;
417 set %unquote(&importlibrary2).%unquote(&XDataset&index);
418 run;
419
420 %end;
421 quit;
422
423 %mend;
424
425 %ChkCombine(ImportLibrary2=in2002,ImportLibrary3=in2003);
MPRINT(CHKCOMBINE): LIBNAME in1002 "C:\Users\<hidden>\SAS Files\B5201002_201709081643";
NOTE: Libref IN1002 was successfully assigned as follows:
Engine: V9
Physical Name: C:\Users\<hidden>\SAS Files\B5201002_201709081643
MPRINT(CHKCOMBINE): LIBNAME in1003 "C:\Users\<hidden>\SAS Files\B5201003_201710101208";
NOTE: Libref IN1003 was successfully assigned as follows:
Engine: V9
Physical Name: C:\Users\<hidden>\SAS Files\B5201003_201710101208
MPRINT(CHKCOMBINE): run;
MPRINT(CHKCOMBINE): proc sql;
MPRINT(CHKCOMBINE): select count(Name) into :NumOfDatasets from Both;
MPRINT(CHKCOMBINE): select Name into :XDataset1-:
MPRINT(TRIM): XDataset24
MPRINT(CHKCOMBINE): from Both;
MPRINT(CHKCOMBINE): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
index = 1
NumofDatasets = 24
XDataset1 = ADVERSE_SACQ
XDataset2 = CNTD_P_SACQ
XDataset24 = VITALS_SACQ
XDataset-index = ADVERSE_SACQ run
MPRINT(CHKCOMBINE): Data Two;
WARNING: Apparent symbolic reference XDATASET not resolved.
MPRINT(CHKCOMBINE): set in2002.ADVERSE_SACQ;
ERROR: Libref IN2002 is not assigned.
MPRINT(CHKCOMBINE): run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TWO may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.TWO was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
index = 2
NumofDatasets = 24
XDataset1 = ADVERSE_SACQ
XDataset2 = CNTD_P_SACQ
XDataset24 = VITALS_SACQ
XDataset-index = CNTD_P_SACQ run
MPRINT(CHKCOMBINE): Data Two;
WARNING: Apparent symbolic reference XDATASET not resolved.
MPRINT(CHKCOMBINE): set in2002.CNTD_P_SACQ;
ERROR: Libref IN2002 is not assigned.
MPRINT(CHKCOMBINE): run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TWO may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.TWO was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Any ideas?
Thank you
UPDATE IT WORKED!!!
Thanks to my last message to Tom explaining what I did, I threw caution to the wind and did this mod to the macro....
LIBNAME &importlibrary2 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS Files\B5201002_201709081643";
LIBNAME &importlibrary3 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS Files\B5201003_201710101208";
Apparently the macro variable @importlibrary2 was not perfectly equal to in2002.... But using the exact same definition, it worked fine...
Thank you to everyone who helped!
I didn't look closely, but it might be the single ampersand here.
&XDataset&index
Thank you... While the double ampersand is the correct form, it isn't the main problem....I had been testing a bunch of different ways - including different number of ampersands, before resorting to posting here - and that was the last test...
This doesn't answer your question but you can simplify your code here:
proc sql;
select count(Name) into :NumOfDatasets from Both;
select Name into :XDataset1-:XDataset%trim(%left(&NumOfDatasets)) from Both;
quit;
To:
proc sql;
select Name into :XDataset1-
from Both;
quit;
%let NumOfDatasets = &sqlobs;
Thank you - sometimes its hard to break old habits 😉
Make sure to reference it consistently. If you want to add a suffix and the evaluate the generated macro variable name then you need to use &&.
data two;
set &importlibrary2..&&XDataset&index..;
run;
Also let PROC SQL count the number of datasets for you. No need to run the query twice to get the count.
proc sql noprint;
select Name
into :XDataset1-
from Both
;
%let NumOfDatasets=&sqlobs;
Thank you. Good catch... That missing semicolon was a last minute add while trying to find where things were going wrong... Fixed.
UPDATE:
I got the macro variables working correctly, but the problem is the library is not being defined in the macro...
In the macro above:
Data Two;
set %unquote(&importlibrary2).%unquote(&XDataset&index);
run;
ERROR: Libref IN2002 is not assigned.
I've read this is a problem with the LIBNAME needing the In2002 to be a global variable, but the solution is to put that into the autoexec.sas... The problem is I don't have access to edit my autoexec.sas on my work pc...
Does anyone have a workaround?
I really don't understand the question.
Normally it is the user of the macro that tells the macro what to do. So if you want the macro to read from a libref named FRED then make sure you have created FRED before you call the macro. Or tell it to use a different libref that does exist.
Say I had a macro named MYMACRO that took as one of its parameters INLIB which should contain the name of the library that it shoudl read from. Then my calling program would look something like:
libname mylib '/mypath';
%mymacro(inlib=mylib);
Heres the full macro (modified slightly from above)
I thought it wasn't reading the value of &&xdataset{index}, but that got fixed...
its just not processing in1002 (I added the two libnames as a last resort - it didn't work either way)
%macro ChkCombine(ImportLibrary2=,ImportLibrary3=);
LIBNAME in1002 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS Files\B5201002_201709081643";
LIBNAME in1003 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS Files\B5201003_201710101208";
run;
proc sql;
select count(Name) into :NumOfDatasets from Both;
select Name into :XDataset1-:XDataset%trim(%left(&NumOfDatasets)) from Both;
quit;
%do index = 1 %to &NumOfDatasets;
%put index = &index;
%put NumofDatasets = &NumofDatasets;
%put XDataset-index = &&XDataset&index;
run;
Data Two;
set %unquote(&&importlibrary2).%unquote(&&XDataset&index);
run;
%end;
quit;
%mend;
%ChkCombine(ImportLibrary2=in2002,ImportLibrary3=in2003);
MPRINT(CHKCOMBINE): LIBNAME in1002 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS
Files\B5201002_201709081643";
NOTE: Libref IN1002 was successfully assigned as follows:
Engine: V9
Physical Name: C:\Users\hannoj05\Desktop\Studies\B520xxxx\SAS Files\B5201002_201709081643
MPRINT(CHKCOMBINE): LIBNAME in1003 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS
Files\B5201003_201710101208";
NOTE: Libref IN1003 was successfully assigned as follows:
Engine: V9
Physical Name: C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS Files\B5201003_201710101208
MPRINT(CHKCOMBINE): run;
MPRINT(CHKCOMBINE): proc sql;
MPRINT(CHKCOMBINE): select count(Name) into :NumOfDatasets from Both;
MPRINT(CHKCOMBINE): select Name into :XDataset1-:
MPRINT(TRIM): XDataset24
MPRINT(CHKCOMBINE): from Both;
MPRINT(CHKCOMBINE): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
index = 1
NumofDatasets = 24
XDataset-index = ADVERSE_SACQ
MPRINT(CHKCOMBINE): run;
MPRINT(CHKCOMBINE): Data Two;
MPRINT(CHKCOMBINE): set in2002.ADVERSE_SACQ;
ERROR: Libref IN2002 is not assigned.
MPRINT(CHKCOMBINE): run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TWO may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.TWO was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
UPDATE IT WORKED!!!
Thanks to my last message to Tom explaining what I did, I threw caution to the wind and did this mod to the macro....
LIBNAME &importlibrary2 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS Files\B5201002_201709081643";
LIBNAME &importlibrary3 "C:\Users\xxxx\Desktop\Studies\B520xxxx\SAS Files\B5201003_201710101208";
Apparently the macro variable @importlibrary2 was not perfectly equal to in2002.... But using the exact same definition, it worked fine...
Thank you to everyone who helped!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.