topic Re: How to report values of a variable from a dataset that are not present in another dataset in SAS Programming

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

anshika — Wed, 14 Oct 2020 14:33:39 GMT

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

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

Kurt_Bremser — Wed, 14 Oct 2020 14:38:23 GMT

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.

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

KathyKiraly — Wed, 14 Oct 2020 14:49:19 GMT

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.

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

anshika — Wed, 14 Oct 2020 15:02:57 GMT

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

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

anshika — Wed, 14 Oct 2020 15:04:53 GMT

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

KathyKiraly — Wed, 14 Oct 2020 15:09:27 GMT

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;

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

Kurt_Bremser — Wed, 14 Oct 2020 15:21:32 GMT

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;

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

anshika — Wed, 14 Oct 2020 15:44:48 GMT

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

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

Kurt_Bremser — Wed, 14 Oct 2020 16:12:03 GMT

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).