Any 'cell' in database NOT CONTAINING a %... change to missing

[NKormanik]

Database (Imported from Excel) contains 'cells' containing a % sign, and cells with no % sign.

Would like to go through entire database, and if NO %, simply DELETE whatever is presently there in the cell.  Result, empty cell.

If there IS a % somewhere in the cell (generally at end, such as +2.07%), leave that cell alone.

Another alternative approach would be to KEEP ONLY those 'cells' where there exists the symbol %.  All other cells should be deleted.  Even better, use RegEx, and insist the % be at the end of the line, such as +2.07%.

Suggestions greatly appreciated.

Thanks!

Nicholas Kormanik

[PhilC]

This is could be handled using Excel's Find and Replace.  ?

[Tom · Accepted solution]

So if any cell in a column contains text like `%` then SAS will define the variable as character.  So you really just need to process the character variables in your dataset.  Regex is overkill for such a simple test.

So if your existing dataset is name HAVE this data step will create a copy into a new dataset named WANT that has the character values modified in the way you want.

data want;
  set have;
  array _ch _character_;
  do index=1 to dim(_ch);
    if not index(_ch[index],'%') then _ch[index]=' ';
  end;
  drop index;
run;

If you want to only do it when the % is at the end then change the test to:

    if not char(_ch[index],length(_ch[index])) = '%' then _ch[index]=' ';