1

I am working in SAS EG and DI, facing a very peculiar problem.

When I look into a column of a dataset in SAS DI Studio or EG, it is appearing fine. But when I paste the data into notepad, some quotes and spaces are appearing.

The data which I am seeing in EG: without spaces

But the same data when copied into Notepad,

extra quotes and spaces are appearing like this(in 6th row):with spaces and quotes

I found this problem when I am using this field as a key in a join, the other related column values for 6th row are not going to the output as the match is failing for that 6th record.

I tried many things like tranwrd,dequote and compress but none of them is changing my result.

Can someone please help in understanding what the problem is and how can this be solved.

Karthaveeryarjun Vinjamoori
  • 473
  • 2
  • 12
  • How did you copy it? Using the mouse? Copied from where? Did you try writing the values to a text file and examining the text file? – Tom Feb 22 '17 at 16:49

1 Answers1

3

Take a look at what is in the column so that you can decide how to handle it. This query will show you both the character string and the Hexadecimal representation of the string.

proc sql;
  select postcode,put(trim(postcode),$hex.) as hexcode,count(*) as nobs
    from x
    group by 1,2
  ;
quit;

So if you see hex characters like 0A, 0D, A0, 08 or other non-printable codes then you can figure out what is happening.

So you might see that you have POSTCODE='LS5 3BT' with HEXCODE='4C533520334254' for most of the records. But perhaps have some that look like the POSTCODE='LS5 3BT', but the value of HEXCODE is something like '0A4C533520334254' which would mean that you have a linefeed character at the beginning of the string. Or perhaps instead of space ('20'X) you have a tab ('09'X) in the middle of the string.

Tom
  • 47,574
  • 2
  • 16
  • 29
  • 1
    You are bang on @Tom . I have got '**0D4C533520334254**' - a CR when ran your query. I removed it using `TRANSLATE(POSTCODE,' ','0D'x)`. OfCourse TRANWRD can also be used. Thanks a ton for helping me in knowing the cause. – Karthaveeryarjun Vinjamoori Feb 23 '17 at 19:06