Using a Piece of Data to Find the Dataset and Library

[Therob107]

Hello all,

I hope everyone's day is going great. I have a question regarding libraries and finding specific data within them without knowing the dataset and field name.  Basically, I am trying to find a particular field name that provides the data I need within a data set. The issue is I have no idea what data set and field name that the information is stored on, the naming convention on the report is not working. I have a record of data from an Excel report and need to find out where it came from.  Is there any SAS features or code I can use to find it?  Many thanks. I apologize if it is something obvious, I am new to SAS. Many thanks.

Sincerely,

Rob

[Reeza]

Are you asking how to find a single data value/point in all your datasets in a library? Do you have reasons to believe it will be unique?

Can't you trace back the source of the report, ie what stored proc did it generate from etc instead?

How do you know the report process didn't calculate the record somehow, especially if it's a percent or average?

You say the naming convention on the report isn't working, does that mean you might be able to search for a variable name or label instead?

[LinusH]

I guess what you are looking for is tractability.

That can be maintained in many ways:

• Metadata - which is based on how data is flowing within a solution/applications. Using SAS Data Integration Studio is the recommendation if you are in the SAS sphere.
• Documentation - tells you how a report is creates and from what data. Not as stringent as active metadata.
• Logging: identifiers and timestamps in your report data that can be traceable with underlying data.

Given you description of the problem, I think you need to find the responsible person for the Excel report, and start you investigation from there.

But, if you have a "unique" key value in your report, it is possible to have a SAS macro search in all your SAS tables for that value.

[ehehedhd · Accepted solution]

Hi Rob,

It's not entirely clear from your email whether you're looking for a particular field without knowing which table it's on, or for a specific data value without knowing which filed and table it's on. The former is much easier than the latter to solve - when you run SAS (or EGuide, etc) it should automatically create libraries in your list, like the WORK library. One of these is SASHELP, and it contains a lot of useful information about the other data in your SAS session - metadata of a sort.

For example, SASHELP.VLIBNAM is a list of the libraries you have assigned (and can be used to find their physical locations, whether they're read-only, on your machine or a server etc etc). SASHELP.VTABLE is basically the next level down, being a list of the various tables which exist.

SASHELP.VCOLUMN might be your friend here, it's a list of columns by library and the table they appear on, which might be a good way to find your mysterious data.

If you are looking for a value rather than a column name, you could use SASHELP.VCOLUMN to narrow down the search, either in a SAS macro or by hand, by finding only the numeric or date columns as appropriate. For character ones, you can also eliminate any which don't have enough length to accomodate what you're looking for. The actual process of searching each column for the value in question might be very slow and tedious though, depending on the size of your library and datasets.

[Therob107]

Hey all,

Thanks for everyone's responses.  Just to clarify, I am not able to speak with the person who previously ran the report. The only shred of data I have is an Excel file outputed from SAS.  However, it looks like the field names have been modified, so they longer match their SAS counterpart.  Is there a way to see the SAS code that generated the Excel output?  Anyway, if not, I can try using VColumns to narrow down the search, even to column types and once I learn more macros. Develop one to help search.  Anyway, thanks for your responses. I greatly appreciate them.

Sincerely,

Rob