topic identical records between 2 datasets in New SAS User

identical records between 2 datasets

rajeshm — Mon, 24 Aug 2020 17:34:57 GMT

I I have 2 datasets (few records duplicated in firstds and those have corresponding records in secondds)

->Find number of records in common between datasets?

-->Elusive to first and second datasets.?

Note: few records have many to many relationship between these datasteps.,guessing my logic is failing here.. please help me.

data both first second;
merge firstds(in=firstinvar) secondds(in=secinvar);
by _all_;
if firstinvar and secinvar then output both;
else if firstinvar then output first;
else if secinvar then output second;
/*put varibles includes firstinvar and secondinvar */ ;
run;

Re: identical records between 2 datasets

cminard — Mon, 24 Aug 2020 18:58:33 GMT

This could really use an example of the data that you're trying to merge together and the desired output. But, a suggestion might be to NOT merge the data. Rather, just combine the two datasets together using a set statement (e.g., "set firstds secondds;"). Then, sort data and remove the observations that you do not want.

Re: identical records between 2 datasets

rajeshm — Tue, 25 Aug 2020 02:39:12 GMT

Thanks for reply, with set we can combine 2 datasets into one. But how can I know exclusive to firt dataset and second dataset and intersected records?? Please provide logic.

Re: identical records between 2 datasets

rajeshm — Tue, 25 Aug 2020 02:41:09 GMT

Thanks for reply, with set we can combine 2 datasets into one. But how can I know exclusive to firt dataset and second dataset and intersected records?? Please provide logic.

Re: identical records between 2 datasets

mkeintz — Tue, 25 Aug 2020 04:39:23 GMT

You say you can have many-to-many matches. So if firstds has 3 records that match 2 records in secondds, how many records do you want in dataset BOTH? 2?, 3?, 5?

Your program as is would put 3 there - i.e. as many records as whichever source has the most for a given match.

Re: identical records between 2 datasets

rajeshm — Tue, 25 Aug 2020 05:08:26 GMT

first  second /*dataset names */
a b    a b  /* varible names */
1 a    1 a
2 b    2 b
2 b    2 b
3 c    3 c
3 c    3 c
3 c    4 d
/*desired output should be */
both   onlyA  onlyB
a b     a b    a b
1 a     3 c    4 d
2 b
2 b
3 c
3 c

please let me know,whether i have given clarity or not??

Re: identical records between 2 datasets

Kurt_Bremser — Tue, 25 Aug 2020 12:14:16 GMT

How can the observation

3 c

be in onlya, when there are two matches for it in dataset second?

Re: identical records between 2 datasets

rajeshm — Wed, 26 Aug 2020 04:44:01 GMT

3 C record

in dataset A 3 times and 2 times in dataset B

I need answer that dataset A has one extra record for 3 C.

purpose: there is db migration happened, i have pulled records from both dwh into sas and have to compare ..

now would like to know

1.only in dataset A(3 c should come here once)

2.only in dataset B(4 D should come here)

4 Both/common in A and B(3 C should come here twice)

dataset differences //Both,exclusiveA,exclusiveB

rajeshm — Wed, 26 Aug 2020 05:24:17 GMT

I have to compare 2 datasets(A and B) and would like to know common records in Both,Exclusive to first and exclusive to second. Please somebody provide the logic

Note:

->1.Dataset A contains duplicate records and Dataset B also contains duplicate records.

-->2.if dataset A cotains 3 records(duplicate) and dataset B contains only 2 of duplicate records , i should get like exclusive to A has one record.

-->3.if dataset B cotains 3 records(duplicate) and dataset A contains only 2 of duplicate records , i should get like exclusive to B has one record.

I struggled a lot because(put log)

amount=944.68
indatasetA=1 indatasetB=0 /* these are in-varibles for datasets with put */
amount=944.68
indatasetA=0 indatasetB=1 /* these are in-varibles for datasets with put */

proc compare

                             Obs ||  sumamount  sumamount      Diff.     % Diff                       
                       ________  ||  _________  _________  _________  _________                       
                                 ||                                                                   
                             12  ||     200.39     200.39  2.842E-14  1.418E-14

Re: dataset differences //Both,exclusiveA,exclusiveB

Kurt_Bremser — Wed, 26 Aug 2020 05:41:30 GMT

Please do not repost the basically same question in different threads, it only makes it harder to follow answers and suggestions. That's why I moved this post back into this thread.

Re: identical records between 2 datasets

Kurt_Bremser — Wed, 26 Aug 2020 05:43:55 GMT

You should first calculate counts by groups for both datasets, and then merge the result, where you filter out those where the counts do not match.

Re: identical records between 2 datasets

Kurt_Bremser — Wed, 26 Aug 2020 07:28:41 GMT

Code would look like this:

proc sql;
create table count_a as
  select
    a,
    b,
    count(*) as count_a
  from a
  group by a, b
;
create table count_b as
  select
    a,
    b,
    count(*) as count_b
  from b
  group by a, b
;
quit;

data
  match
  only_a
  only_b
  more_in_a
  more_in_b
;
merge
  count_a (in=in_a)
  count_b (in=in_b)
;
by a b;
if in_a and in_b
then do;
  if count_a = count_b then output match;
  else if count_a > count_b then output more_in_a;
  else output more_in_b;
end;
else if in_a then output only_a;
else output only_b;
run;

Re: dataset differences //Both,exclusiveA,exclusiveB

rajeshm — Wed, 26 Aug 2020 09:54:39 GMT

i am not yet tested your code But can you let me know what to do in this below case.

My log:

amount=944.68 /* dataset varible and its value */
indatasetA=1 indatasetB=0 /* these are in-varibles for 2 datasets*/
amount=944.68
indatasetA=0 indatasetB=1

my question: here it should be indatasetA=1 indatasetB=1 but why considering those values as different?

Later compared with proc and got so many as below ,same value but difference is there, thats why i am failing to implement my logic,in this case what to do?

proc compare

                             Obs ||  sumamount  sumamount      Diff.     % Diff                       
                       ________  ||  _________  _________  _________  _________                       
                                 ||                                                                   
                             12  ||     200.39     200.39  2.842E-14  1.418E-14

Re: dataset differences //Both,exclusiveA,exclusiveB

Kurt_Bremser — Wed, 26 Aug 2020 10:06:14 GMT

Numeric precision; because of the fact that all calculations (and storage) are done in binary, results of different calculation paths (or different imports from a database) can differ in minuscule amounts. To get exact matches, apply the ROUND function.

Re: identical records between 2 datasets

rajeshm — Wed, 26 Aug 2020 11:02:12 GMT

Thanks a ton .

I got the expected result after applying round function with your logic.

i confused a lot with proc compare and put logs but after applying round function,but no where found the information to apply round...

Re: dataset differences //Both,exclusiveA,exclusiveB

rajeshm — Thu, 27 Aug 2020 14:42:26 GMT

while reading from database into sas ,

I put length and format+informat (format+informat 11.2, length 😎

proc sql;
select
AMT   as sumamount format=11.2 informat=11.2 length=8,
from  mytable f
where xx condition;
quit;

it should read from database as 11.2 informat only right ? but after reading into sas. 234.44 is not equal to 233.44 (pulled from another dwh) this is happening for few records only.

Thanks in advance

Re: identical records between 2 datasets

rajeshm — Thu, 27 Aug 2020 15:33:34 GMT

How can i achieve Datasets "more_in_a and "more_in_b" with proc sql?

Datasets "Match,only_a,only_b" can be achieved with proc sql.

Re: identical records between 2 datasets

Kurt_Bremser — Thu, 27 Aug 2020 17:07:26 GMT

@rajeshm wrote:

How can i achieve Datasets "more_in_a and "more_in_b" with proc sql?

Datasets "Match,only_a,only_b" can be achieved with proc sql.

Maxim 14: Use the Right Tool.

The right tool is the data step, so you use it.

Re: dataset differences //Both,exclusiveA,exclusiveB

Kurt_Bremser — Thu, 27 Aug 2020 17:09:21 GMT

The format does not change the value; to align values, use the ROUND function, as already suggested.

Re: dataset differences //Both,exclusiveA,exclusiveB

rajeshm — Tue, 01 Sep 2020 13:42:29 GMT

Agreed.

format is for presenting the data and informat is for reading the data, means that i told sas to read in that way.

when i checked my data also it showed me 143.24 like that. but later problem started.

proc sql;
create table mytable as
select
f.TRANSACTION_AMT as sumamount format=11.2 informat=11.2 length=8,
from xxxxxx
quit;

Even when i checked the data it showed me 143.24 in sas but finally faced problems with this kind of issues(143.24!=143.24) .thats why i would like to know. my surprise is for few records it matched but for problem.i confused a lot.

Dont mind for my stupid questions but what i read about informat is not matching here.