topic Re: Identify match in 2 column in SAS Programming

Identify match in 2 column

Kelly_S — Sun, 28 Jul 2019 20:46:00 GMT

Hello,

I need help in building a code for a specific criteria. I have a very huge dataset with lots of variables and observations.

I need to identify the repetitive pair of observations/values in 2 columns by month/year

In this given dataset, the column 1 AND column 2 is a character Variable contains either name or id.

Jan Month have these values get repeated in Feb month,

(a,b) , (c,e or e,c) , (7,p or p,7), (6, u or u,6) .

Also Jan month and April month have a pair (1,2 or 2,1) and so do March..

I need to pick only these pair matches in any of the months and store it in different datasets.

I am trying my code in Proc Sql. Thanks for looking.

Given Dataset:

year Months Column 1- Name or ID Column2- Name or ID

17 Jan a b

17 Jan 1 2

17 Jan c e

17 Jan 5 9

17 Jan g h

17 Jan 7 p

17 Jan u 6

17 feb a b

17 feb 11 2

17 feb e c

17 feb 7 p

17 feb 6 u

17 Mar c e

17 Mar 6 u

17 Apr 2 1

18 Jan 1 2

18 Jan g h

18 feb a b

18 mar 5 b

Re: Identify match in 2 column

PGStats — Sun, 28 Jul 2019 22:14:45 GMT

Please provide the desired output for your example data.

Re: Identify match in 2 column

Reeza — Sun, 28 Jul 2019 22:17:29 GMT

Please show the SQL code you've tried. SQL doesn't do well for detecting sequences.

Re: Identify match in 2 column

PaigeMiller — Sun, 28 Jul 2019 22:39:53 GMT

To identify the matches, I would use PROC FREQ

/**** UNTESTED CODE ****/

proc freq data=have noprint;
    table col1*col2/out=counts;
run;

proc sql;
    create table want as select h.* from
        have as h left join counts as c on h.col1=c.col1 and h.col2=c.col2
        where c.count>1;
quit;

This will create a table of rows that have at least one matching pair of col1 and col2. From there, I leave it up to you to split these into separate data sets (something which is usually not recommended, by the way).

If this does not work for some reason, then you would need to provide your example data as a SAS data step, following these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

and also clearly state the desired results.

Re: Identify match in 2 column

Kelly_S — Sun, 28 Jul 2019 22:53:01 GMT

something like this,
Below code picks the one's that has pairs repeated, but i cant group by month.
I also have another variable called id's, its unique to each pair for each month.

proc sql;
create table want as
select col 1,
col 2,
count(distinct id) as ct
from have
group by
col1,col2
having count(distint id) >=2;run;

Re: Identify match in 2 column

Kelly_S — Sun, 28 Jul 2019 23:01:18 GMT

Historically i wanted to see how many times the pair gets repeated for each month . I am still thinking about my output. But I am trying to output somewhat like this,

2017 2018
Jan
Feb
Mar
Apr
May
June

Re: Identify match in 2 column

PGStats — Mon, 29 Jul 2019 02:54:08 GMT

Something like this?

data have;
length col1 col2 $8;
input year Month $ Col1 Col2;
date = input(cats("01",month,year), date.);
format date mmyy.;
drop year month;
datalines;
 17 Jan a b
 17 Jan 1 2
 17 Jan c e
 17 Jan 5 9
 17 Jan g h
 17 Jan 7 p
 17 Jan u 6
 17 feb a b
 17 feb 11 2
 17 feb e c
 17 feb 7 p
 17 feb 6 u
 17 mar c e
 17 mar 6 u
 17 Apr 2 1
 18 Jan 1 2
 18 Jan g h
 18 feb a b
 18 mar 5 b
;

data temp;
set have; by date;
call sortc(col1, col2);
run;

proc sort data=temp; by col1 col2 date; run;

data want;
set temp; by col1 col2 date;
if not (first.col2 and last.col2);
run;

proc print data=want; run;

Re: Identify match in 2 column

Kurt_Bremser — Mon, 29 Jul 2019 05:43:32 GMT

Create a concatenated key, eg

matchkey = catx('|',col1,col2);

Then sort by that key, year and month.

After that, a double DOW can do it:

data want;
do until (last.matchkey);
  set have;
  by matchkey;
  flag = not first.matchkey;
end;
do until (last.matchkey);
  set have;
  by matchkey;
  if flag then output;
end;
drop flag;
run;