topic output observations in one dataset but not in another in SAS Programming

output observations in one dataset but not in another

Fluvio1 — Wed, 24 Jul 2024 15:10:47 GMT

I am using Proc Compare to try and select the observations in one dataset, but not in the other, and output the observations. There does not seem to be an out= option that does this ? Please advise. Here is my code:

proc contents data=work.import_163;
run;
*proc print;
*run;
proc sort data=work.import_163;
by Wrs_acres;
run;

data Merge_148_163;
merge work.import_148 work.import_163 ;
by WRS_acres;
run;

Proc print data=Merge_148_163;
run;
proc contents data=Merge_148_163;
run;

proc compare base=work.import_163 compare=work.import_148
out=diff outnoequal noprint;
id wrs_acres;
run;

proc print data=diff ;
by wrs_acres;
id wrs_acres;
title 'The Output Data Set RESULT';
run;

Re: output observations in one dataset but not in another

Tom — Wed, 24 Jul 2024 15:15:36 GMT

If you want to match the observations on the key variables then just use a data step to split the data into matching and non matching datasets.

So something like this.

data both left_only right_only;
  merge left(in=inleft) right(in=inright);
  by keys ;
  if inleft and inright then output both;
  else if inleft then output left_only;
  else output right_only;
run;

If you want EXACT matches then make the BY statement include ALL of the variables.

Re: output observations in one dataset but not in another

ballardw — Wed, 24 Jul 2024 15:32:57 GMT

The options would be OUTBASE if you want the observation from the BASE data set or OUTCOMP if you want them from the COMPARE set.

Note that a variable telling you which data set the observations come from is added:

data work.base;
   set sashelp.class;
run;

data work.comp;
   set sashelp.class;
   if sex='M' and name not in ('Alfred' 'Robert')then height=height+1;
run;

proc compare base=work.base compare=work.comp noprint
   out=work.different outnoequal outcomp;
run;

proc compare base=work.base compare=work.comp noprint
   out=work.different2 outnoequal outbase;
run;

You can run this as you should have the SASHELP.CLASS data set.

Since you did not provide any actual data or what you want to see from the result it is hard to tell if this actually matches your expectations.

Re: output observations in one dataset but not in another

Fluvio1 — Thu, 25 Jul 2024 14:20:00 GMT

I tried both programs but they do not provide the results I am looking for. The attached dataset "Compare_dataset_148_obsv" contains 148 observations. The dataset "Compare_dataset_214_obsv" contains 214 observations. I would like to identify the observations in "Compare_dataset_214_obsv" that are not in "Compare_dataset_148_obsv" and output the observations to a file. While the datasets contain different variables, they have in common: WRS_acres.

Re: output observations in one dataset but not in another

Reeza — Thu, 25 Jul 2024 14:39:35 GMT

proc sql;
create table data_want as 
select * from compare_dataset_214_obsv
where wrs_acres not in (select distinct wrs_acres from compare_dataset_148_obs);
quit;

@Fluvio1 wrote:

I tried both programs but they do not provide the results I am looking for. The attached dataset "Compare_dataset_148_obsv" contains 148 observations. The dataset "Compare_dataset_214_obsv" contains 214 observations. I would like to identify the observations in "Compare_dataset_214_obsv" that are not in "Compare_dataset_148_obsv" and output the observations to a file. While the datasets contain different variables, they have in common: WRS_acres.