How to report values of a variable from a dataset that are not present in another dataset

[anshika]

How to report values of a variable from a dataset that are not present in another dataset

[Kurt_Bremser]

Use a hash object.

For code examples, supply example data in usable form (data steps with datalines), so we have something to develop and test against.

[anshika]

Data file1;
Set one;
Input prsnid $ ;
Datalines;
11
12
13
14; run;

Data file2;
Set two
Input prsnid $ gracedate yymmdd10.;
Datalines;
11 2020-12-31
12 2020-12-31
14 2020-01-01; run;

I need to report prsnid=13 in excel sheet because that is missing from second dataset

[KathyKiraly]

This code will return 13, which is in file1, but not file2.

data file1;
  input prsnid $ ;
datalines;
11
12
13
14
; 
run;

data file2;
 input prsnid $ gracedate yymmdd10.;
datalines;
11 2020-12-31
12 2020-12-31
14 2020-01-01
; 
run;
proc sql;
  select prsnid
    from file1
  except 
  select prsnid
    from file2
;
quit;

[Kurt_Bremser · Accepted solution]

data check;
set file1;
if _n_ = 1
then do;
  declare hash f2 (dataset:"file2");
  f2.definekey('prsnid');
  f2.definedone();
end;
if f2.check() ne 0; /* indicates "not found" */
run;

[anshika]

Yes this code worked. Thanks.
But can I use multiple datasets in place of File2?

[Kurt_Bremser]

You can have multiple hash objects in your data step. The limiting factor is the memory available vs. the size of the objects (variable(s) size * number of items + overhead for building the btree).

Each "lookup" dataset will have its own object with its own methods (f3.check(), f4.check(), and so on).

[KathyKiraly]

If I understand your question, you can use the EXCEPT operator in PROC SQL. It will display values of a variable that are present in the first data set, but not in the second data set. Here is some code to test.

data one;
  input num;
cards;
1
2
3
4
;
run;
data two;
  input num;
cards;
1
2
6
7
;
run;
proc sql;
  select num
    from one
  except
  select num
    from two
;
quit;

The final result will display 3 and 4.

[anshika]

Data file1;
Set one;
Input prsnid $ ;
Datalines;
11
12
13
14; run;

Data file2;
Set two
Input prsnid $ gracedate yymmdd10.;
Datalines;
11 2020-12-31
12 2020-12-31
14 2020-01-01; run;

I need to report prsnid=13 in excel sheet because that is missing from second dataset