Character variable is a period, how to delete?

[Lefty]

Hi,

I have a variable, HCPCS, that is usually numeric, but sometimes is alphanumeric (e.g. G0202). I am merging several wide datasets together, so most of them contain only numeric values for HCPCS but once I merge a file that has an alpha code, the HCPCS variable becomes a SAS-defined character variable. The problem is, when I make the data long, I would like to delete all the rows that have missing values for HCPCS, but since it's a character variable, I'm having trouble. I can delete the ones that are blank (if HCPCS=" " then delete) but when I try to delete the ones that are periods (.), they don't get deleted. I've tried if HCPCS="." then delete (which doesn't delete anything) and if HCPCS=. then delete (which converts HCPCS to a numeric variable and deletes all the codes with letters in them, which I don't want to lose).

ANy help would be greatly appreciated! Thanks.

Laurie

[Linlin]

try:

if missing(HCPCS) then delete;

[Lefty]

Sorry, I should have clarified, I tried that too. That only deleted the ones that were " " type missing. For all the ones that were . missing, they were still there. Thanks though.

[LinusH · Accepted solution]

Sounds like that you don't really have the control whther the variable is numeric or not. It should be possible to have that, or how does your merge operation look like?

If you are sure that HCPCS is char, try left(HCPCS) = '.', or just compress(HCPCS,'.') = ' '.

[Lefty]

This code worked:

if left(HCPCS)='.' then delete;

THANK YOU!

[Tom]

When SAS does an automatic conversion from numeric to character you will see those types of right aligned values.  The worst is when the variable is longer than you screen size so when you browse the data it looks all blank.  Try this little example to see what is happening.

data check;

  length char $10 ;

  input x;

  char=x;

  put char= $quote. x=;

cards;

1

.

3

run;

You can use the CATS() function to remove the leading blanks.  The nice thing is that it will work without errors or warnings with both character and numeric variables.

data test;

  set check;

  if cats(char) in (' ','.') then delete;

  put char= $quote. x=;

run;

data test;

  set check;

  if cats(x) in (' ','.') then delete;

  put char= $quote. x=;

run;