03-29-2012 10:06 AM
I have a requirement to extract the data from sqlserver 2005 database into SAS Datasets. Iam new to SAS Dataset formats.
I studied that for a SAS Dataset there will be two files
1) .DAT File which contains the data
2) .SAS File which contains the control structure.
Now my query is do we have to generate both files or .DAT file is enough?
Does any one have idea of generating those files. Iam using C#.Net for extraction.
Please point me to any resources regarding this.
Thank you Very much for your time.
03-29-2012 12:20 PM
SAS can read SQL/Server databases directly, so there is no need for the external program.
The way you are describing would generate some sort of text file and the SAS code for reading it. Unless you put a lot of effort into generating the SAS code, you are going to lose a lot of the metadata from SQL/Server. Technically, you don't "need" the SAS code. However, if you don't you will need other documentation for the .DAT file and you will have unhappy users.
03-30-2012 01:04 AM
Hello Doc Muhibaier,
Thank you for your response.
Althogh SAS can read SQL/Server databases directly it willn't work for us.
We can't provide access to SQL Server database for the SAS Team. They are of different consultants. The only option we have is to provide the data in SAS Datasets.
Now my issue is how to generate SAS Datasets using C# programming.
03-30-2012 08:55 AM
If you do not have SAS and/or SAS cannot access your database then the easiest thing to do is to generate a raw data file (frequently called an ASCII file) and a program that can read it. Mostly likely it is easiest to generate a CSV file as that format is pretty well known. But watch out for issues like embedded CR of LF characters in your data. If the data is very well formatted the you might be able to get away with only the CSV file. But when you have things like missing values, character variables with only digits so that they look like numbers or characters fields with wildly varing length the CSV can be hard to interpret. If you users are experienced programmers then you might just generate the CSV file and data dictionary that decribes the variables. An experienced user can use that to write their own program to read the CSV or at least QC the results of PROC IMPORTs attempt to determine the variable types. But if you need to it should not be difficult to generate a simple SAS data step to read in your data based on the metadata you have about the variables.
You might also want to look at using a utility such as STAT/Transfer that can convert the files for you on your machine. That way you could generate SAS datasets and send those.