Desktop productivity for business analysts and programmers

Using a Piece of Data to Find the Dataset and Library

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Using a Piece of Data to Find the Dataset and Library

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


Accepted Solutions
Solution
‎10-31-2013 12:11 PM
New Contributor
Posts: 2

Re: Using a Piece of Data to Find the Dataset and Library

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.

View solution in original post


All Replies
Super User
Posts: 19,194

Re: Using a Piece of Data to Find the Dataset and Library

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?

Super User
Posts: 5,391

Re: Using a Piece of Data to Find the Dataset and Library

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.

Data never sleeps
Solution
‎10-31-2013 12:11 PM
New Contributor
Posts: 2

Re: Using a Piece of Data to Find the Dataset and Library

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.

New Contributor
Posts: 4

Re: Using a Piece of Data to Find the Dataset and Library

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 563 views
  • 3 likes
  • 4 in conversation