BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lefty
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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,'.') = ' '.

Data never sleeps

View solution in original post

5 REPLIES 5
Linlin
Lapis Lazuli | Level 10

try:

if missing(HCPCS) then delete;

Lefty
Obsidian | Level 7

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
Tourmaline | Level 20

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,'.') = ' '.

Data never sleeps
Lefty
Obsidian | Level 7

This code worked:

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

THANK YOU!

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 5753 views
  • 0 likes
  • 4 in conversation