BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stuart_snap
Fluorite | Level 6


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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

5 REPLIES 5
Patrick
Opal | Level 21

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.

jakarman
Barite | Level 11

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 --<-----
AndrewHowell
Moderator

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).

garander
Calcite | Level 5

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.

stuart_snap
Fluorite | Level 6

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 go;)

Thanks!

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

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