topic Re: SUBSTITUTE ELEMENTS OF ONE DATASET WITH ANOTHER DATASET BY MATCHING ID in New SAS User

SUBSTITUTE ELEMENTS OF ONE DATASET WITH ANOTHER DATASET BY MATCHING ID

AB90 — Sun, 30 May 2021 15:41:45 GMT

Hi,

Given to dataset like below. I am trying to find a way to create a third dataset that is equal to dataset 2 but has

all the values of dataset 1 in injury when id_company is the same.

I would much appreciate any help.

/* Data set 1 */
data t2005;
input id_company injury;
datalines;
123 0
245 1
322 0
355 1
4 1
5 0
666 0
7 0
;
run;

/*Data set 2 */
data t2006;
input id_company injury;
datalines;
123 1
245 1
322 0
343 0
4 1
5 1
677 0
7 0
;
run;

Re: SUBSTITUTE ELEMENTS OF ONE DATASET WITH ANOTHER DATASET BY MATCHING ID

sbxkoenk — Sun, 30 May 2021 17:19:56 GMT

Hello,

I think it's this what you want but please check thoroughly.

proc sort data=t2005; by id_company; run;
proc sort data=t2006; by id_company; run;
data temp;
 update t2006 /* Put master-data-set first     */
        t2005 /* Then put transaction-data-set */
		;
 by id_company;
run;

PROC SQL noprint;
 create table dataset3 as
 select *
 from temp
 where id_company IN (select id_company from t2006)
 ;
 QUIT;
/* end-of-program */

Koen

Re: SUBSTITUTE ELEMENTS OF ONE DATASET WITH ANOTHER DATASET BY MATCHING ID

Tom — Sun, 30 May 2021 18:09:53 GMT

Sounds like you just want to MERGE the datasets and only keep the observations that were already in the first one.

data t2005;
  input id_company injury;
datalines;
123 0
245 1
322 0
355 1
4 1
5 0
666 0
7 0
;

data t2006;
  input id_company injury;
datalines;
123 1
245 1
322 0
343 0
4 1
5 1
677 0
7 0
;

proc sort data=t2005 ;
  by id_company;
run;
proc sort data=t2006 ;
  by id_company;
run;

data want ;
  merge t2005(in=in1) t2006(in=in2);
  by id_company;
  if in1;
run;

proc print;
run;
proc compare data=t2005 compare=want;
  id id_company;
run;

Results:

Obs    id_company    injury

 1           4          1
 2           5          1
 3           7          0
 4         123          1
 5         245          1
 6         322          0
 7         355          1
 8         666          0

The COMPARE Procedure
Comparison of WORK.T2005 with WORK.WANT
(Method=EXACT)

Data Set Summary

Dataset              Created          Modified  NVar    NObs

WORK.T2005  30MAY21:14:08:51  30MAY21:14:08:51     2       8
WORK.WANT   30MAY21:14:08:51  30MAY21:14:08:51     2       8


Variables Summary

Number of Variables in Common: 2.
Number of ID Variables: 1.


Observation Summary

Observation      Base  Compare  ID

First Obs           1        1  id_company=4
First Unequal       2        2  id_company=5
Last  Unequal       4        4  id_company=123
Last  Obs           8        8  id_company=666

Number of Observations in Common: 8.
Total Number of Observations Read from WORK.T2005: 8.
Total Number of Observations Read from WORK.WANT: 8.

Number of Observations with Some Compared Variables Unequal: 2.
Number of Observations with All Compared Variables Equal: 6.


Values Comparison Summary

Number of Variables Compared with All Observations Equal: 0.
Number of Variables Compared with Some Observations Unequal: 1.
Total Number of Values which Compare Unequal: 2.
Maximum Difference: 1.


All Variables Compared have Unequal Values

Variable  Type  Len  Ndif   MaxDif

injury    NUM     8     2    1.000


The COMPARE Procedure
Comparison of WORK.T2005 with WORK.WANT
(Method=EXACT)

Value Comparison Results for Variables

____________________________________________________________
             ||       Base    Compare
 id_company  ||     injury     injury      Diff.     % Diff
 __________  ||  _________  _________  _________  _________
             ||
          5  ||          0     1.0000     1.0000          .
        123  ||          0     1.0000     1.0000          .
____________________________________________________________