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
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;
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.
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
Are your account number a fixed length? If so, using FINDW or INDEXW is probably much easier than SCAN and looping.
Please provide at least one concrete example, NOT in Excel, and the desired result.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.