03-24-2015 06:44 AM
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
where user_Id in ( '456225', '589635', '145123'); /* These user_Id were taken from fees paid details*/
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
03-24-2015 07:19 AM
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.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 */
if myhash.find()=0 then output old;
else output new;
/*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*/
03-26-2015 05:18 AM
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.
03-24-2015 07:25 AM
/* User_ids in fees not participation */
create table RESULT1 as
select distinct USER_ID
where USER_ID not in (select distinct USER_ID from PARTICIPATION);
Reverse the datasets to get the reverse.
03-24-2015 08:34 AM
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