05-10-2016 04:26 AM
SAS has all its metadata stored in views/datasets within the SASHELP library (or DICTIONARY if you prefer SQL). Hence there is no need to for messy macro code, simple Base SAS will suffice:
libname tmp "s:\temp\rob"; %let library=TMP; data want; set sashelp.vslib (where=(libname="&library.")); run;
If you need further example of how to do something, please post test data - in the form of a datastep, with the required output.
05-10-2016 06:50 AM
Well, that lets you see if a libref is assigned, but it doesn't really return any message/code.
data want; rc = libref("&library"); sysmsg = sysmsg(); run;
05-10-2016 07:16 AM
Thanks for your replies.
basically i am checking if library exists or not, if lib does not exist i need to write that error message as an observation to the output (like below-i have used macro code because i am running this in loop transformations in di studio)
%if %sysfunc(libref(&library.)) ne 0 %then %do;
%put ERROR: &library. library does not exist;
errormessage=&library. library does not exist;
%if %sysfunc(exist(&library..&table.)) = 0 %then %do;
%put ERROR: &table. dataset does not exist;
Finally, i should get output table with following observations
1.&library. library does not exist;
2. &table. dataset does not exist;
any ideas? thanks in advance
05-10-2016 07:54 AM
Not sure why "must" use macro. Are you executing this in the inner or the outer job?
Nevertheless, I think you already got enough examples to able to make this happen. Try this outside DI Studio first with manually assigned libref/macro variables.
But if a libref is not assigned, I guess the job will abend anyhow, or do you wish to use this information to skip the loop iteration?
05-10-2016 08:22 AM
Yes I am working in EG, i need to check for library, table and column existence and if any errors then writing error messages into dataset. In di studio, i have got parameters for library, column and table etc... running in inner loop
I didnt assign libref, just gave the values of library and table, and it worked
Anyways i tried the following and it works.
length errormessage $200.;
05-10-2016 08:03 AM
Sounds to me like your just making problems for yourself. Why would a library not exist? Surely you need to know what libraries to setup during initialisation? I agree with @LinusH, the examples above will get you started, but I think addressing the problem at the larger scale would be more efficient.
05-10-2016 08:30 AM
For checking data quality, we had an excel with libraries, tables and columns (adding these manually in the excel). For example, if someone wrongly enter the lib name or table name we will stop running the other jobs which check data quality. so, by checking the lib,ltable and column existence if any errors we are writing that to output dataset so that later on we can check it and correct the problem.
I hope this makes sense.
05-10-2016 08:40 AM
Sorry, it doesn't. Are you saying your metadata is controlled using Excel? There is where your problem lies then. Excel is an unvalidated, unstructured, file format, and importing that into SAS can lead to multiple guessing etc. As I mentioned above, fix the problem at its source and this one goes away. Address the startup mechanics so that proper libraries are setup etc. and then you don't need to check it.
As for the other parts, am not sure what you mean, strcuture can easily be checked via the metadata views/datasets in SASHELP library, VTABLES and VCOLUMNS has all the relevant information, just write datasteps against that?
data want; set sashelp.vcolumn (where=(library="WORK" and memname="TMP")); if name not in (<list of valid var names>) then output; run;
For instance gives you a dataset with any variables which are not in a prescribed list.
05-10-2016 08:55 AM
we register excel files into sas using microsoft excel database, and we are having some global macro variables that will convert the excel data into proper sas dataset. So there will be no problems by using excel.
Yes i know as you said we can check the structure of data by using sashelp, and in many ways too
i have got only problem with how to record error message as an observation each time within the loop and it is solved now.
Thanks for your time.