sas/sql data storage advice
-
has anyone dealt with this before? I think I just need to be pointed in the right direction. But my time is very limited.
I have data that is basically formatted for sas. Instead of nulls there are large negative values and "." - thus storing it as numeric is not as practical as it should be. In addition the negative values are translated to chars. These values are mixed in with a wide range of other values, numeric, int, char, etc. The data is used by both SQL and SAS. The tables are very WIDE. storing as varchar quickly exceeds the row size limit.
So suggestions? Brainstorming i came up with...storing as single string, storing as xml, storing as text and accessing through link server, storing as numeric, int or what ever and using a function to populate ".".
let me know if i need to provide more info.
Thanks so much!
-
OK, I may be pretty ignorant here but what's "SAS"?
Aside from that, and either way, it depends:
(Most importantly) For what purpose do you need to store the information in SQL Server?
And in case I'm not the only one who isn't familiar with SAS, maybe you can provide an example of a column & values and qty of columns, tables, rows.
-
Would the function ISNUMERIC() help in filtering numeric data from character data? You could use it in a CASE statement:
CASE
when ISNUMERIC(value) then <numeric value processing>
else <character value processing>
END
Another thought, if the tables are too wide you coudl to vertical partitioning: two tables, same primary key in each, data entry has to ensure that every row entered in one table has to have a related row (same key) in the other table. This lets you split the data across two (or more) tables.
Philip
-
If your sql-column is defined nullable (. gets null), there should be no problem with SAS.
This is what we use to bulkload data using SAS.
%let sqlDWH =
provider="sqloledb"
properties=('data source'='mysqlserver' 'Initial Catalog'='mydb'
'Persist Security Info'='True'
'user id'='myuser' 'password'='mypwd') schema='dbo' ;
OPTIONS ERRORABEND;
libname sqlDWH oledb &sqlDWH BCP=YES DBCOMMIT=50000;
libname sasDWH 'v:\sasdwh_dv\dimensions\dimuser';
/* Load the data into the BO SQL Server */
PROC SQL;
CONNECT TO oledb
AS sql (&sqlDWH);
EXECUTE (truncate table mytable) BY sql;
DISCONNECT FROM sql;
QUIT;
PROC APPEND BASE=sqlDWH.mytable=sasDWH.mytable;
RUN;
I hope this helps
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
yes, thank you. Not having to import '.' is good.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply