turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How do I use SCAN for multiple numbers with IF sta...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-21-2016 11:44 AM

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

Accepted Solutions

Solution

09-28-2016
01:34 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to esvenson

09-21-2016 07:32 PM

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;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to esvenson

09-21-2016 11:52 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to esvenson

09-21-2016 01:26 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-21-2016 02:00 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to esvenson

09-21-2016 02:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-21-2016 02:07 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to esvenson

09-21-2016 05:15 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

09-21-2016 06:04 PM

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;

Solution

09-28-2016
01:34 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to esvenson

09-21-2016 07:32 PM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

09-28-2016 01:35 PM

Hi ballardw,

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

~ Eric

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

~ Eric