LIBNAME error in sas Macro

[Kp1234]

Hello, 

I am creating a sas macro to read metadata file and assign libname depending on value obtained from metadata file.

Code:

*Read metadata file (works fine)*

PROC IMPORT OUT= Work.Metadata DATAFILE= "/home/pandek1/Reporting_DataMart/Input/Reporting_DataMart.xlsx"
DBMS=xlsx REPLACE;
SHEET="Sheet1";
GETNAMES=YES;
RUN; 

*Macro to assign libname*

%Macro Study_list();

proc sql print;
select distinct(Study) into :studylist separated by ' '
from Metadata;
quit;

%do i=1 %to %sysfunc(countw(&studylist,%str( )));
%let study_list=%scan(&studylist,&i,' ');
%put &study_list;

%global ta compd study; *Works Fine*
data _null_;
length ta compd study $ 10;
ta=substr("&study_list",1,2);
compd=substr("&study_list",3,3);
study=substr("&study_list",7,3);
run;
%let ccvpath="/gbs/prod/clin/data/&ta./&compd./&study./stable/blinded/level0";
libname Input "&ccvpath" access=readonly;

libname Input clean;
%end;

%mend Study_list;

%Study_list;

*SAS log message*

/gbs/prod/clin/data/ca/209/77T/stable/blinded/level0

ERROR: The CLEAN engine cannot be found.

ERROR: Error in the LIBNAME statement.

I have tried using libname function inside data step but to no use. 

Example: *libname(libref,ccvpath,);

The idea is to loop through libname and extract data depending on each libname value. 

Thank you in advance.

[PaigeMiller]

%let ccvpath="/gbs/prod/clin/data/&ta./&compd./&study./stable/blinded/level0";
libname Input "&ccvpath" access=readonly;

libname Input clean;

Perhaps you mean

libname Input clear;

Otherwise, I don't understand what you are trying to do, you need to explain what this LIBNAME is supposed to be doing.

[LinusH]

I guess you mean CLEAR?

[Kp1234]

Thank you for the replies. It is indeed 'clear' .

But the problem still persists.

Log error:

WARNING: Libref INPUT is not assigned. 

Attaching new log.

[Tom]

These two statements do not make much sense.

%let ccvpath="/gbs/prod/clin/data/&ta./&compd./&study./stable/blinded/level0";
libname Input "&ccvpath" access=readonly;

First you are referencing three other macro variables to define CCVPATH.

Do all of those macro variables have the value you expected?

Where did you create them?  I do not see any code trying to create macro variables name TA, COMPD or STUDY in any of the code you posted.

And even if you did give them values you would end up generating libname statement with very strange use of quotes.

libname Input ""/gbs/prod/clin/data/TA/COMPOUND/STUDY/stable/blinded/level0"" access=readonly;

Because of the strange quotes SAS will probably think that statement as 6 words in it instead of 4.

libname Input "" /gbs/prod/clin/data/TA/COMPOUND/STUDY/stable/blinded/level0 "" access=readonly;

So it is going to see an empty path.  Then a option name it does not understand. Then another quoted empty string. And finally the ACCESS= option.

[Reeza]

Do you know that there's a libname function?

So a data step is easier than macro especially for debugging.

proc sort data=metadata;
by study;
run;

data create_libraries;
set metadata;
by study;
retain count;
if first.study then do;
ta=substr(study, 1, 2);
compd=substr(study, 3,3);
study = substr(study, 7, 3);

path = catx( '/', '/gbs/prod/clin/data/', ta, compd, study, 'stable/blinded/level0');
count+1; 
lib_name = catt('input', put(count, z2.));
rc = libname(lib_name, path);
end;

run;

You can't create multiple libnames with different locations. They'll overwrite each other, so only the last library will be relevant. 

Each library needs a unique name. 

You can create a single library that points to multiple locations but if the files have the same name that's problematic. Which one are you trying to do?

---

@Kp1234 wrote:

Hello, 

I am creating a sas macro to read metadata file and assign libname depending on value obtained from metadata file.

Code:

*Read metadata file (works fine)*

PROC IMPORT OUT= Work.Metadata DATAFILE= "/home/pandek1/Reporting_DataMart/Input/Reporting_DataMart.xlsx"
DBMS=xlsx REPLACE;
SHEET="Sheet1";
GETNAMES=YES;
RUN; 

*Macro to assign libname*

%Macro Study_list();

proc sql print;
select distinct(Study) into :studylist separated by ' '
from Metadata;
quit;

%do i=1 %to %sysfunc(countw(&studylist,%str( )));
%let study_list=%scan(&studylist,&i,' ');
%put &study_list;

%global ta compd study; *Works Fine*
data _null_;
length ta compd study $ 10;
ta=substr("&study_list",1,2);
compd=substr("&study_list",3,3);
study=substr("&study_list",7,3);
run;
%let ccvpath="/gbs/prod/clin/data/&ta./&compd./&study./stable/blinded/level0";
libname Input "&ccvpath" access=readonly;

libname Input clean;
%end;

%mend Study_list;

%Study_list;

*SAS log message*

/gbs/prod/clin/data/ca/209/77T/stable/blinded/level0

ERROR: The CLEAN engine cannot be found.

ERROR: Error in the LIBNAME statement.

I have tried using libname function inside data step but to no use. 

Example: *libname(libref,ccvpath,);

The idea is to loop through libname and extract data depending on each libname value. 

Thank you in advance.

---

[Tom]

The idea is to loop through libname and extract data depending on each libname value. 

You don't appear to have any attempt to "extract" data.  What does that mean?

[Tom]

Since you seem to by pulling only 8 characters from the variable STUDY why not just use those characters as the name of the libref?

PROC IMPORT
  DATAFILE= "/home/pandek1/Reporting_DataMart/Input/Reporting_DataMart.xlsx"
  DBMS=xlsx 
  OUT= metadata REPLACE
;
RUN; 

data metadata ;
  set metadata;
  length libref $8 ta compd studynum $10 path $200;
  ta=substr(study,1,2);
  compd=substr(study,3,3);
  studynum=substr(study,7,3);
  path=catx('/','/gbs/prod/clin/data',ta,compd,studynum,'stable/blinded/level0');
  libref=cats(ta,compd,studynum);
  rc=libname(libref,path,,'access=readonly');
run;

[ballardw]

You have definition for a path as:

 80         %let ccvpath=/gbs/prod/clin/data/&ta/&compd/&study/stable/blinded/level0;

Where are the macro variables &ta, &compd and &study defined?

Does your file system actually start at /gbs  as a drive or or mount point? Your Proc Import code, which you do not have in the log text, starts at /home so I suspect when you use that path in the Libname statement that is the problem, either undefined macro variables, incorrect start of path or both.

[Kurt_Bremser]

This does not make sense:

data_null_;
length ta compd study $ 10;
ta=substr("&study_list",1,2);
compd=substr("&study_list",3,3);
study=substr("&study_list",7,3);
run;
%let ccvpath="/gbs/prod/clin/data/&ta./&compd./&study./stable/blinded/level0";

You create three data step variables, which immediately vanish once the data step terminates.

And then you try to use three macro variables with the same names, which are never defined anywhere in your code.

You probably have messages about undefined macro variables in your log.

The macro language provides the %SUBSTR function, if you need to define macro variables.