Architecting, installing and maintaining your SAS environment

Data formatting in SAS Management Console?

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Data formatting in SAS Management Console?


Hi all,

We are a small company with a new SAS install (4 users, including myself).  Our data structure was not set up with SAS in mind, so we are encountering some hurdles.

I am acting as the administrator and have full access to Management Console.  We are accessing our SQL Server using an OBDC connection. I have successfully mapped several libraries and we have access to all the data we need in SAS.  However, the formatting and naming conventions in the existing SQL Server tables are quite bad (spaces, numbers as characters, weird dates, etc).  Is there some way in Management Console to rename and format data?  So that the users see a clean, appropriately formatted dataset?

I have been able to make changes to the tables in the console, but the changes don't seem to feed through to our actual EG users.  I don't want to ask our SQL Server guys to make the changes on their end, because that would effect other reporting within the company.

Any suggestions from anyone?  I apologize if I am not asking clearly... I came from a big company where we had an admin team, so I am not used to handling these specific type issues.

Thanks!

Stuart


Accepted Solutions
Solution
‎12-23-2013 05:47 PM
Respected Advisor
Posts: 3,900

Re: Data formatting in SAS Management Console?

You could define your libraries using the meta engine and then register all tables belonging to the library. Then you could amend column formats and labels in these tables to what you need.

You then would need to make sure that EG users use the table metadata definitions: SAS(R) 9.4 Language Interfaces to Metadata

What you can't do via table metadata is changing column names and types. Especially ugly are column names with blanks. This will cause a lot of pain for downstream SAS processing as such variable names need to be addressed as literals in the form 'colum name'n.  Not sure that all EG wizards will do this always.

As always issues are best resolved at the source. If it's too late to set up clean tables then what always can be done is to define views over these tables with "clean" column names and attributes. That's what I would aim for. And actually: It's even good practice to have such a view layer between data base tables and data consumers.

As for weird dates: The SAS Access to ODBC engine will convert date and datetime fields in a data base correctly to SAS date and datetime values. But if a DBA decided to store a date in another field type (eg. an INTEGER) then how would SAS know that this is a date and convert it. So this is also something you could fix in the views.

If it's no option to get the views in the SQL database then you could also define such views in SAS and then have the libname in metadata point to these views. But this would only be a work-around and not really the right thing to do.

View solution in original post


All Replies
Solution
‎12-23-2013 05:47 PM
Respected Advisor
Posts: 3,900

Re: Data formatting in SAS Management Console?

You could define your libraries using the meta engine and then register all tables belonging to the library. Then you could amend column formats and labels in these tables to what you need.

You then would need to make sure that EG users use the table metadata definitions: SAS(R) 9.4 Language Interfaces to Metadata

What you can't do via table metadata is changing column names and types. Especially ugly are column names with blanks. This will cause a lot of pain for downstream SAS processing as such variable names need to be addressed as literals in the form 'colum name'n.  Not sure that all EG wizards will do this always.

As always issues are best resolved at the source. If it's too late to set up clean tables then what always can be done is to define views over these tables with "clean" column names and attributes. That's what I would aim for. And actually: It's even good practice to have such a view layer between data base tables and data consumers.

As for weird dates: The SAS Access to ODBC engine will convert date and datetime fields in a data base correctly to SAS date and datetime values. But if a DBA decided to store a date in another field type (eg. an INTEGER) then how would SAS know that this is a date and convert it. So this is also something you could fix in the views.

If it's no option to get the views in the SQL database then you could also define such views in SAS and then have the libname in metadata point to these views. But this would only be a work-around and not really the right thing to do.

Valued Guide
Posts: 3,208

Re: Data formatting in SAS Management Console?

Use information map studio SAS(R) Information Map Studio 4.2: Getting Started with SAS(R) Information Maps

The disadvantage can be additional overhead, the advantage is a semantic separation

---->-- ja karman --<-----
Moderator
Posts: 238

Re: Data formatting in SAS Management Console?

Jaap - I actually like what InfoMaps does & often recommend it specifically as the "SAS Semantic Layer". It protects analysts (who are often very good at obtaining & manipulating data, but not managing it) from complex data structures, plus it protects the data from analysts by controlling what is surfaced to the users & how (prompts, authentication-based pre-filters, etc).

New Contributor
Posts: 3

Re: Data formatting in SAS Management Console?

I'd suggest a strong look at the options available to fix the data and/or views at source as per Patrick's reply.  This will be best for minimising issues in the future as your data holdings grow.

Contributor
Posts: 24

Re: Data formatting in SAS Management Console?

Thanks for the suggestions.  I have spoken with my database people and we were able to clean up the most pressing fields.  I have written some small macros for my users to fix other issues. All in all, we have we what we need for now.... we will learn as we goSmiley Wink

Thanks!

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 758 views
  • 9 likes
  • 5 in conversation