topic Re: Merging two datasets in New SAS User

Merging two datasets

nduksy — Thu, 07 Oct 2021 14:16:14 GMT

Hello All,

I have two datasets I will like to merge, they both have the same columns. I want to check to see if the counter column for a row has changed, if it hasn't, do nothing, if it has, create a new row

Dataset A

ID	RunDate	Count
S001	06OCT2021	6
S002	06OCT2021	2

Dataset B

ID	RunDate	Count
S001	07OCT2021	6
S002	07OCT2021	5
S003	07OCT2021	1

MERGED - The resultant dataset compares datasets A and B and will only to Dataset A where a record from B where the ID is same, but the Count is different and will also add a new row if an ID that isn't in A is now in B.

ID	RunDate	Count
S001	06OCT2021	6
S002	06OCT2021	2
S002	07OCT2021	5
S003	07OCT2021	1

I hope my explanation makes sense.

Thanks in advance.

Re: Merging two datasets

Kurt_Bremser — Thu, 07 Oct 2021 14:22:25 GMT

Merging in SAS means to put data side-by-side. What you intend to do here is called appending or stacking.

You can use BY with a SET and use the resulting LAST. variable:

data want;
set
  a
  b
;
by id count; /* add notsorted after count if counts can decrease */
if last.count;
run;

Untested; for tested code, provide data in (correctly working!) data steps with datalines.

Re: Merging two datasets

Reeza — Thu, 07 Oct 2021 14:54:15 GMT

SQL Union which will not insert duplicate rows?

proc sql;
create table3 as
select * from table1
union 
select * from table2
quit;

Or UPDATE on all three columns?

data want;
update A B;
by ID RunDate Count;
run;

Untested.

Re: Merging two datasets

Sajid01 — Thu, 07 Oct 2021 15:38:55 GMT

Hello @nduksy
The following code should give what you want.

data one ;
informat rundate date9.;
format rundate date9.;
input ID $ 	RunDate 	Count;
datalines;
S001 	06OCT2021 	6
S002 	06OCT2021 	2
;
run;
data two ;
informat rundate date9.;
format rundate date9.;
input ID $	RunDate 	Count;
datalines;
S001 	07OCT2021 	6
S002 	07OCT2021 	5
S003 	07OCT2021 	1
;
run;
/* merge the datasets*/
data merged;
merged two one;
by ID count;
run;

data merged;
merge two one;
by ID count;
run;

data merged;
merge two one;
by ID count;
run;

The output will be as follows

And lastly ,in future, please post the tables as a datastep .

Re: Merging two datasets

nduksy — Thu, 07 Oct 2021 17:59:17 GMT

Thank you. This works as I'd like it to. The only problem I have is that the runDate for all the records is showing the runDate for the records in the second dataset. I want to maintain the runDates from their respective datasets. See my code below:

data keep.blankreport2;
	set keep.blankreport keep.blankrun%sysfunc(today(),date9.);
	by datasetName count;
	if last.count;
run;

Re: Merging two datasets

nduksy — Thu, 07 Oct 2021 18:00:48 GMT

I reordered the datasets in my set statement and that seems to have fixed that.