topic Re: Macro variable not being read... in ODS and Base Reporting

Macro variable not being read...

JamesH — Fri, 27 Oct 2017 17:09:24 GMT

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

Re: Macro variable not being read...

WarrenKuhfeld — Fri, 27 Oct 2017 17:12:43 GMT

I didn't look closely, but it might be the single ampersand here.

&XDataset&index

Re: Macro variable not being read...

Reeza — Fri, 27 Oct 2017 17:17:07 GMT

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;

Re: Macro variable not being read...

Tom — Fri, 27 Oct 2017 17:21:01 GMT

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;

Re: Macro variable not being read...

Cynthia_sas — Sat, 28 Oct 2017 00:44:44 GMT

Also, look at line 413 in your log and then at the result of the %PUT in the log:
XDataset-index = ADVERSE_SACQ run

--this looks like your %PUT statement is missing a semi-colon and that the string 'run' is being taken as part of the value of your macro variable.

Cynthia

Re: Macro variable not being read...

JamesH — Mon, 30 Oct 2017 12:06:27 GMT

Thank you - sometimes its hard to break old habits 😉

Re: Macro variable not being read...

JamesH — Mon, 30 Oct 2017 12:18:15 GMT

Thank you. Good catch... That missing semicolon was a last minute add while trying to find where things were going wrong... Fixed.

Re: Macro variable not being read...

JamesH — Mon, 30 Oct 2017 12:26:55 GMT

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

Re: Macro variable not being read...

JamesH — Mon, 30 Oct 2017 13:50:56 GMT

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?

Re: Macro variable not being read...

Tom — Mon, 30 Oct 2017 14:17:09 GMT

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

Re: Macro variable not being read...

JamesH — Mon, 30 Oct 2017 16:53:18 GMT

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

Re: Macro variable not being read...

JamesH — Mon, 30 Oct 2017 16:58:01 GMT

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!