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
- /
- Data manage

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-16-2017 09:56 PM

Dear expert,

I got an uneasy task. I have two columns of data say X1 and X2 each in 3 digits but assigned as characters. Each column has over 6000 rows. I need to physically combine X1 and X2 and make them six digits. First X1 with X2 and then X2 with X1. I could do that by CATT function, and the product is like X12 and X21. This is how it becomes:

X1 X2 X12 X21

361 362 361362 362361

845 846 845846 846845

846 845 846845 845846

845 846 845846 846845

......

Now the problem is, I need to count X12 or X21 in such a way that 845846 and 846845 are considerd exactly same. So in the above example, the entry 845846 should count 3 not 2 or 1. PROC FREQ for X12 or X21 would count only 2 or 1 respectively. I tried defining like:

if X12=845846 and X21=846845 then X21=X12;

it works with PROC FREQ of X21, but to write for each of the 6000 entries is almost impossible, if not impossible.

I would be very grateful if any kind soul can help me out with an better easier solution.

Thank you,

Sijansap

Accepted Solutions

Solution

08-22-2017
09:01 AM

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

Posted in reply to sijansap

08-16-2017 10:43 PM

Create a sorted version of your concatenated variable:

```
if X1 < X2 then sX12 = catt(X1, X2);
else sX12 = catt(X2, X1);
```

then do your freq count on sX12.

PG

All Replies

Solution

08-22-2017
09:01 AM

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

Posted in reply to sijansap

08-16-2017 10:43 PM

Create a sorted version of your concatenated variable:

```
if X1 < X2 then sX12 = catt(X1, X2);
else sX12 = catt(X2, X1);
```

then do your freq count on sX12.

PG

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

Posted in reply to PGStats

08-17-2017 08:56 AM

Thank you very much PGStats. The only problem could be - even though they are digits but they are considered as characters since some of them start like 001, 020 etc. Anyway, I like your idea of sorted CATT and the comparing the values between X1 and X2. I certainly appreciate your suggestion and will try it out.

Thanks again.

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

Posted in reply to sijansap

08-17-2017 08:12 PM

Thanks again PGStats.

I switched X1 and X2 in the inequality to preseve zeroes, and worked perfectly.

I truly appreciate your help.

Sijansap