topic Re: Any 'cell' in database NOT CONTAINING a %... change to missing in SAS Programming

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

NKormanik — Fri, 05 Feb 2021 08:41:42 GMT

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

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

PhilC — Fri, 05 Feb 2021 19:18:13 GMT

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

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

Tom — Fri, 05 Feb 2021 19:25:27 GMT

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]=' ';