How do I use SCAN for multiple numbers with IF statement?

[esvenson]

Hello SAS experts,

I am reading in a data set from Excel into SAS 9.3 and one of the columns in Excel is filled with multiple account numbers.  Each Excel cell could have 0, 1 or multiple account numbers that are later being used to join two sets by.  The SCAN function needs to be capable of checking for an account number match on the first, second, etc for the numbers present. Ideally, it would be nice to have an array in use to help with scenarios where there are more account numbers than expected.  I was thinking something like this below but I'm having trouble rounding out how it could be done.  Your help is greatly appreciated.

data xyz_cv_&datamonth.;

set abc_cv_&datamonth.;

array c;
do i = 1 to dim(c);
c{i} = scan(account_number, i, ',');

if account_key = account_number then 1

else......
end;
drop i;

Thank you in advance,

Eric

[ballardw]

One would assume there is something between the accounts so you should be able to to use:

Do i = 1 to (countw(account_number));

Array may not be helpful unless you are going to refer to the values multiple times. And the maximum array size has to be set explicitly.

Possibly just a temp account number value is all you need:

TempAccount= scan(account_number,i);

But that may not be needed at all. If you are looking to see if the ACCOUNT_KEY value is in that list then FINDW or INDEXW may suffice.

If Findw(account_number,Account_key) > 0 then do;

   <some code>;

end;

else do;

   <some code>;

End;

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we may have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

[Reeza]

The problem is with your IF condition.

You use SCAN to extract the account number into c(i) but then you didn't use it in the IF condition, you used the account number.

if account_key = c(i) then flag=1;
else....

Your other option is to search the field instead, using FIND/FINDW or INDEX/INDEXW

[esvenson]

Hi Reeza,

Thanks so much for your help. So if the account_key does NOT equal the account_number on the first account_number that shows up in the cell, the array will instruct the scan to keep checking, correct? And the only thing remaining after that would be what action to take if there are either zero accounts or none that match? Thank you again for your help. It is very much appreciated.

- Eric

data xyz_cv_&datamonth.;
set abc_cv_&datamonth.;
array c;
do i = 1 to dim(c);
c{i} = scan(account_number, i, ',');
if account_key = c(i) then flag=1;
else......
end;
drop i;

[Reeza]

Are your account number a fixed length? If so, using FINDW or INDEXW is probably much easier than SCAN and looping.

[esvenson]

Reeza,
Unfortunately, some account numbers are 10 digits, some are 13, etc. What would you recommend in that scenario? Fortunately, the numbers are straightforward without characters like a dash to strip out. Thank you for your help.

[ballardw]

Please provide at least one concrete example, NOT in Excel, and the desired result.

[esvenson]

ballardw,

This is a very simplified version what I have in Excel.  I have a table containing the following fictitious data below.  The Account_Key is in a SAS data set and it and the Excel data will be combined in the final data set.  I need to determine if the account_key is found in the account_number column in Excel.  If the account_key isn't matched on the first number in the account_number column, I need SAS to cycle through the other numbers for each record to see if there is a match.  If there is a match on any of x quantity of numbers, then there is a match and it will be included in the final data set.  I thought it would be ideal to have an array because I have no way of knowing how many account numbers will show each month. 

Account_Key        Account_Number 

143029277               51469475, 84746179470012, 51434214642, 143029277, 7481242214700144    

9797411248             8487416411, 2121116401, 9797411248, 00949412735,

564917623111         48716461, 9754642133, 564917623111, 9754347554197200

data xyz_cv_&datamonth.;
set abc_cv_&datamonth.;
array c;
do i = 1 to dim(c);
c{i} = scan(account_number, i, ',');
if account_key = c(i) then flag=1;
else......(NOT SURE WHAT TO ADD HERE)
end;
drop i;

[ballardw · Accepted solution]

If the requirement is if the Account_key matches any of the values to keep the record then this demonstrates a way using FINDW.

I included a record without a match to show that it gets filtered out.

data have;
   infile datalines  dlm=',' dsd;
   informat Account_Key $13. Account_Number $100.;
   input Account_Key        Account_Number ;
datalines;
143029277    ,     "51469475, 84746179470012, 51434214642, 143029277, 7481242214700144"
9797411248   ,      "  8487416411, 2121116401, 9797411248, 00949412735,"
564917623111  ,     " 48716461, 9754642133, 564917623111, 9754347554197200"
123456,      14569000000
;
run;

data want;
   set have;
   If findw(Account_number,strip(account_key),' ,','E')>0 ;
run;

[esvenson]

Hi ballardw,
Sorry for the late response. Thank you so much for your time in solving this. I really appreciate your help.

~ Eric