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
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]=' ';
This is could be handled using Excel's Find and Replace. ?
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]=' ';
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.