What is the infile SAS equivalent in SQL Server?

Posted on

Question :

I am trying to read a csv file in my SQL Server Script. In SAS, I can read it using the infile option. (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm)

I know that I can achieve the same thing in SQL Server using BULK INSERT. (https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017) What I can’t figure out is some of the equivalent option in SQL.

Specifically, in SQL I want to achieve the functionality of Missover, DSD, FirstObs and LRECL provided by SAS. I am confident that FirstObs equivalent is Firstrow, I am not sure about Missover and LRECL and I cannot figure out DSD all together.

Any help/suggestion much appreciated.

enter image description here

Answer :

As per MSSQLTIPS documentation Transferring data from SAS to SQL Server and back SQL Server data typing is much richer than SAS, translating SQL Server values for use with SAS can be challenging. Even the topic of representing and processing missing data values is substantially different between SQL Server and SAS. SQL Server represents missing data values with NULL values, but SAS has a minimum of two ways of representing missing values: a period (.) for numeric missing values and a blank space (‘ ‘) for character missing values.

As DSD (delimiter-sensitive data) tells SAS to treats two
consecutive delimiters as a missing value and removes quotation marks
from character values.

Whereas SQL Server represents missing data
values with NULL values.

For further your ref here & here

Leave a Reply

Your email address will not be published. Required fields are marked *