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
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.
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.
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.
Thanks again PGStats.
I switched X1 and X2 in the inequality to preseve zeroes, and worked perfectly.
I truly appreciate your help.
Sijansap
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.