topic Re: Why wrong data picked in SAS Programming

Why wrong data picked

HeatherNewton — Mon, 19 Dec 2022 03:22:59 GMT

Data application (keep=org_code application_ref_no application_no credit_card_no applicant _id);
Set onenov.application (encoding=any);
Where credit_card_no in (’0004835199841570016’, ’0004835191842460016’,
’0000235881284822010’, ...) /* plus another 17 similar codes*/
Run;

I have the above code but strangely in the output I get credit no wrong for those not end in 0
Eg 0004835199841570016 becomes
0004835199841570010

While those end in zero is correct

Is the format issue?

Re: Why wrong data picked

HeatherNewton — Mon, 19 Dec 2022 03:27:59 GMT

Oh I tested again with only 6 codes and it works fine

What is the best way to do using many ORs instead in (,.,.,.,.,.)?

Re: Why wrong data picked

ballardw — Mon, 19 Dec 2022 03:39:33 GMT

SHOW the format and length associated with the variable. If you don't know how to find that then run:

Proc contents data=onenov.application;
run;

and share the results. There may be issues with the data.

Also, any time you have questions about code behavior you should copy the code and all messages from the log and paste that into a text box. Sometimes there are messages about data conversion. Also if you have errors then a possible previous data set might be what you are looking at, not the results of this particular code.

Also be aware that the code you pasted shows single quotes that are curly or "smart quotes" see: (’0004835199841570016’,

These cause errors. You want to make sure that the values use the simple quote ' as typed form the keyboard. They may appear as curly because of pasting into the main message window, which on this forum will reformat pasted text. It is a good idea to paste code or log entries into a text box opened on the forum with the </> icon to prevent such.

An example of what the wrong quote can do:

333  data example;
334     set junk;
335     where credit_card_no=’0004835199841570016’;
                             -
                             22
                             76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
              a numeric constant, a datetime constant, a missing value, (, *, +, -, :, INPUT,
              NOT, PUT, ^, ~.

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

336  run;

Re: Why wrong data picked

HeatherNewton — Tue, 20 Dec 2022 09:59:26 GMT

if I just want a subset of the dataset, what is the best way to write where

if I have many variables and I only want the the following:

org_code='301', account_no='1234567890123456'

org_code -'251', account_no=9876543210123456'

...

can I write:

where

org_code='301' and account_no='1234567890123456'

org_code='251' and account_no='9876543210123456'

...

(and 20 more pairs...) ;

Re: Why wrong data picked

ballardw — Tue, 20 Dec 2022 11:37:09 GMT

You want to ( ) around the Pairs to make sure they are considered as such.

where
   ( org_code='301' and account_no='1234567890123456' )
   or
   ( org_code='251' and account_no='9876543210123456')
   or
   ( <continue the pattern>)

If, and this is a big if, your data is nice about combinations that occur in your data you might be able to use shorter code such as

where 
    org_code in ('301'  '251' <other codes>)  and account_no in ('1234567890123456'  '9876543210123456' <other accounts>)

This may be possible if you know, meaning have checked, that org_code ='301' never appears with any of the account_no except '1234567890123456'. This would select org_code='301' and account_no='9876543210123456' if it occurs for example.

Please post code in text boxes opened using the </> icon.

That could be checked by any number of methods

Re: Why wrong data picked

Tom — Tue, 20 Dec 2022 15:38:04 GMT

You could convert the pairs into a single string so you could use IN

where catx('|',org_code,account_no) in
 ('301|1234567890123456'
  '251|9876543210123456'
  ...
 )

Re: Why wrong data picked

Kurt_Bremser — Tue, 20 Dec 2022 16:17:20 GMT

Put the codes (or code combinations) you want into their own dataset and do a join.