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.
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.
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.
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;
I reordered the datasets in my set statement and that seems to have fixed that.
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.
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 .
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.