I ran into the same problem when saving results from Microsoft SQL Server Management Studio 2005. The problem is that it saves outputs in UNICODE by default. You can change this. When saving results click on the arrow on the save button > save with encoding > set 'Available encodings' to ANSI.

Also, it may be safer to save as tab delimited. If there is a comma in the field you will have problems. Another useful feature I have found is using the coalesce function to replace 'NULL's with blanks so they import properly into SAS. If anyone knows of a better way to do this then filling your queries with coalesce functions (i.e. in SQL rather than recoding in SAS), I would be interested in the solution.

Regards, Scott Bucher SAS Programmer Office of Accountability NYC Dept. of Education

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Tina MayIAsk Sent: Wednesday, June 18, 2008 6:00 PM To: SAS-L@LISTSERV.UGA.EDU Subject: format issue: read CSV file into SAS

Hi,

I have a CSV file which I saved from a result generated by Transact-SQL. It looks all right. But when I imported it into SAS, all these columns turned out to have a wired looking. I tried both proc import and data step, and have the same issue.

Here is an example.

This is the CSV file.

24822326,54020 ,59016 ,1.0000

24842915,64106 ,65010 ,1.0000

24840873,54040 ,58042 ,5.0000

24843485,64007 ,65007 ,2.0000

24840722,54036 ,59002 ,4.0000

24843485,64106 ,65010 ,2.0000

24840878,54027 ,59039 ,2.0000

Then I ran the following code

*data* sub.substitution1;

length var1 var2 var3 var4 $*12*;

infile "C:\testfile1.CSV" dsd truncover;

input var1 $ var2 $ var3 $ var4 $;

*run*;

*proc* *print* data=sub.substitution1;*run*;

Here is the result

var1 var2 var3 var4

ÿþ2 4 8 2 2 5 4 0 2 0 5 9 0 1 6 1 . 0 0 0 0

2 4 8 4 2 9 6 4 1 0 6 6 5 0 1 0 1 . 0 0 0 0

2 4 8 4 0 8 5 4 0 4 0 5 8 0 4 2 5 . 0 0 0 0

2 4 8 4 3 4 6 4 0 0 7 6 5 0 0 7 2 . 0 0 0 0

2 4 8 4 0 7 5 4 0 3 6 5 9 0 0 2 4 . 0 0 0 0

2 4 8 4 3 4 6 4 1 0 6 6 5 0 1 0 2 . 0 0 0 0

2 4 8 4 0 8 5 4 0 2 7 5 9 0 3 9 2 . 0 0 0 0

I opened SAS dataset; there is a square between each digit for each variable. For example the first row of var2 looks like 5□4□0□2□0□. (Not sure if the square symbol will show up in this eamil, but anyway there is a square between these numbers). I checked the attribute of the column, it is $12.

Does anyone know how to get rid of these squares and make all these columns truly $12.?

Thank you very much.

Tina