topic Re: Removing records with variable value contain '-' in SAS Programming

Removing records with variable value contain '-'

newboy1218 — Wed, 24 Jul 2019 23:20:09 GMT

Hi.. I have a dataset where a variable called ACCT_NO (character).. In there, some of the records have ACCT_NO contain '-' and some don't.. My goal is to remove those have '-' in their ACCT_NO and turn the rest's ACCT_NO into numeric..

Here is my code but I got the error message.. Anyone know what the error is? Thanks!

data datasetB;
	set datasetA;
	if ACCT_NO like '%-%' then delete;
	ACCTKEY = input(ACCT_NO,19.);
run;

8548   data datasetB;
8549       set datasetA;
8550       if ACCT_NO like '%-%' then delete;
                      ----
                      388
                      76
ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Removing records with variable value contain '-'

novinosrin — Wed, 24 Jul 2019 23:24:54 GMT

data datasetB;
	set datasetA;
	if not index(ACCT_NO,'-') ;
	ACCTKEY = input(ACCT_NO,best.);
run;

Re: Removing records with variable value contain '-'

newboy1218 — Wed, 24 Jul 2019 23:31:11 GMT

But doesn't INDEX function return the position of a string? In that case, shouldn't I use this instead?

if index(ACCT_NO,'-') = 0;

Re: Removing records with variable value contain '-'

Tom — Wed, 24 Jul 2019 23:31:32 GMT

The data step is not going to recognize LIKE as an operator. That only works in PROC SQL code or in WHERE statements.

Re: Removing records with variable value contain '-'

newboy1218 — Wed, 24 Jul 2019 23:32:29 GMT

I see. Thanks!

Re: Removing records with variable value contain '-'

novinosrin — Wed, 24 Jul 2019 23:33:26 GMT

yes you can which is the equivalent of NOT logical operatior(=0)

@newboy1218 wrote:

But doesn't INDEX function return the position of a string? In that case, shouldn't I use this instead?
if index(ACCT_NO,'-') = 0;

perator. Your style is just as good.

Re: Removing records with variable value contain '-'

novinosrin — Wed, 24 Jul 2019 23:35:19 GMT

Test this

data have;
input ACCT_NO :$10.;
cards;
123-678
4567
4657-78
57567
;

data want;
	set have;
	if not index(ACCT_NO,'-') ;
	ACCTKEY = input(ACCT_NO,best.);
run;

Re: Removing records with variable value contain '-'

ChrisNZ — Wed, 24 Jul 2019 23:48:00 GMT

Also:

data datasetB;
  set datasetA;
  where ACCT_NO not like '%-%' ;

The like operator is not as efficient as the index function though.

Re: Removing records with variable value contain '-'

newboy1218 — Wed, 24 Jul 2019 23:48:57 GMT

I am gonna use 'index' now. But thank you!

Re: Removing records with variable value contain '-'

newboy1218 — Wed, 24 Jul 2019 23:50:37 GMT

For 'index' can I check more than 1 strings?

for example, can I do

if not index(ACCT_NO, '-') and not index(ACCT_NO, '/')?

Re: Removing records with variable value contain '-'

novinosrin — Wed, 24 Jul 2019 23:52:35 GMT

Yes indeed. Now you are writing a compund expression wth logical operators NOT, AND 🙂

Re: Removing records with variable value contain '-'

ChrisNZ — Wed, 24 Jul 2019 23:53:28 GMT

It would have been faster to test than to ask.

Yes you can.

Re: Removing records with variable value contain '-'

novinosrin — Wed, 24 Jul 2019 23:56:18 GMT

If your requirement is below unlike your original,

Try indexC

For Example

data have;
input ACCT_NO :$10.;
cards;
123-678
4567
4657-/78
57567
;

data want;
	set have;
	if  indexc(ACCT_NO,'-/')=0 ;
	ACCTKEY = input(ACCT_NO,best.);
run;