How to find a observation present between 2 data sets(By comparing one data set with the other).?

Reply
Contributor
Posts: 20

How to find a observation present between 2 data sets(By comparing one data set with the other).?

Hi All,

I am new to the community, Presently I am dealing with of the issue, I have 2 data sets namely

1. Fees paid details contains 5000 records

2.Participation details contains 138000 records,

Both these sets contain a common field 'User_ID' ,

I want to see , if those user_id in fees paid details data set are present in participation_details data set or no ( i.e To check if these 5000 records are there in the participation_details data set )

I tried using where statement

Data Participant_V1;

set Participant;

where user_Id in ( '456225', '589635', '145123');  /* These user_Id were taken from fees paid details*/

run;

Since I can't paste all the 5000 records manually, How can  this be done using code So that all the records which are present in participation data sets will be shown, I tried using merge statement, but was not successful.

Any quick help would be highly helpful

Feespaid_details.pngParticipant_details.png

Thanks

Pra

Frequent Contributor
Posts: 115

Re: How to find a observation present between 2 data sets(By comparing one data set with the other).?

Have you tried proc compare or merge/ join methods?

You can use hash:

data old new;

     if _n_=1 then do;

          declare hash myhash(dataset: 'dataset1');

          myhash.definekey('user_id');

          /*     myhash.definedata(); shouldn't need to definedata unless dataset1 isn't fully included in dataset2 and you wish to create a 3rd dataset for those obscure records */

          myhash.definedone();

     end;

     set dataset2;

     if myhash.find()=0 then output old;

     else output new;

run;

/*logic is if record from table2 is found in table1 you output old (all methods return code 0 if they are succesful)

otherwise output new*/

Contributor
Posts: 20

Re: How to find a observation present between 2 data sets(By comparing one data set with the other).?

Hi Naveen,

Thanks for your help,  I was able to get the required solution, However I have new 2 requirements which i realized 1. 'Discount' Should be equal and not be greater then OLDINS_CD and Branch should exactly match with the  both the data sets.

Regards

Pra

Super User
Super User
Posts: 7,720

Re: How to find a observation present between 2 data sets(By comparing one data set with the other).?

Hi,

/* User_ids in fees not participation */

proc sql;

     create table RESULT1 as

     select     distinct USER_ID

     from       FEES

     where    USER_ID not in (select distinct USER_ID from PARTICIPATION);

quit;

Reverse the datasets to get the reverse.

Contributor
Posts: 20

Re: How to find a observation present between 2 data sets(By comparing one data set with the other).?

Hello,

This worked for me, I did something like this

However, I have two more condition that is  'discount' should not be greater then OLDINS_CD, and branch should match exactly in both the datasets,

thanks once again

Procsql.png

Ask a Question
Discussion stats
  • 4 replies
  • 261 views
  • 0 likes
  • 3 in conversation