I need to append new data to historical data on a daily bases.
my question here is?
historical data on rdbms side
table1
Id | dose1 date | dose2date | updated date | |
---|---|---|---|---|
21 | 3/14/2010 | 4/4/2012 | 04/04/2012 | |
23 | 3/4/2010 | 04/04/2012 | ||
62 | 3/4/2010 | 4/3/2012 | 04/04/2012 | |
67 | 3/4/2010 |
| 04/04/2012 | |
98 | 3/4/2010 |
| 04/04/2012 | |
99 | 3/4/2010 | 04/04/2012 | ||
123 | 3/4/2010 |
| 04/04/2012 |
but on 4/10/2012 the table changes as follows on rdbms side
table 2
Id | dose1 date | dose2date | updated date | |
---|---|---|---|---|
21 | 3/14/2010 | 4/4/2012 | 04/04/2012 | |
23 | 3/4/2010 | 4/8/2012 | 04/10/2012 | |
62 | 3/4/2010 | 4/3/2012 | 04/04/2012 | |
67 | 3/4/2010 |
| 04/04/2012 | |
98 | 3/4/2010 |
| 04/04/2012 | |
99 | 3/4/2010 | 4/8/2012 | 04/10/2012 | |
123 | 3/4/2010 |
| 04/04/2012 | |
126 | 4/3/2011 | 4/9/2012 | 4/10/2012 |
I need to bring to sas daatamart.
query ran on '4/4/2012'
libname rdbms 'pw path to rdbms';
proc sql;
create table t as
select *
from table 1
where updateddate='4/4/2012';
quit;
result is data set 1 from above query.
Id | dose1 date | dose2date | updated date | |
---|---|---|---|---|
21 | 3/14/2010 | 4/4/2012 | 04/04/2012 | |
23 | 3/4/2010 | 04/04/2012 | ||
62 | 3/4/2010 | 4/3/2012 | 04/04/2012 | |
67 | 3/4/2010 |
| 04/04/2012 | |
98 | 3/4/2010 |
| 04/04/2012 | |
99 | 3/4/2010 | 04/04/2012 | ||
123 | 3/4/2010 |
| 04/04/2012 |
but query on 04/10/2012
proc sql;
create table t as
select *
from table 2
where updateddate='4/10/2012';
quit;
the dataset 2 from above query
Id | dose1 date | dose2date | updated date |
---|---|---|---|
23 | 3/4/2010 | 4/8/2012 | 04/10/2012 |
99 | 3/4/2010 | 4/8/2012 | 04/10/2012 |
126 | 4/3/2011 | 4/9/2012 | 4/10/2012 |
I need to append data set 1 and 2
on'4/10/2012'
proc append base=dataset1 data=dataset2;
quit;
I need to append id 126 to dataset1 and need to update id 23 and id 99 in data set with latest dose2date.
thanks
Hi,
Try this..hope it helps
data one;
input dose1 date dose2date updateddate;
informat date dose2date updateddate mmddyy10.;
format date dose2date updateddate date9.;
cards;
21 3/14/2010 4/4/2012 04/04/2012
23 3/4/2010 . 04/04/2012
62 3/4/2010 4/3/2012 04/04/2012
67 3/4/2010 4/3/2012 04/04/2012
98 3/4/2010 4/3/2012 04/04/2012
99 3/4/2010 . 04/04/2012
123 3/4/2010 4/3/2012 04/04/2012
;
run;
data two;
input dose1 date dose2date updateddate;
informat date dose2date updateddate mmddyy10.;
format date dose2date updateddate date9.;
cards;
21 3/14/2010 4/4/2012 04/04/2012
23 3/4/2010 4/8/2012 04/10/2012
62 3/4/2010 4/3/2012 04/04/2012
67 3/4/2010 4/3/2012 04/04/2012
98 3/4/2010 4/3/2012 04/04/2012
99 3/4/2010 4/8/2012 04/10/2012
123 3/4/2010 4/3/2012 04/04/2012
126 4/3/2011 4/9/2012 4/10/2012
;
run;
proc sort data=one;by dose1;run;
proc sort data=two(where=(updateddate='10APR2012'd));by dose1;run;
data want;
update one two;
by dose1;
run;
Thanks,
Shiva
Hi,
Sorry it updates complete row(updatecolumn will also get updated) ....above solution will not work for this scenario.
Thanks,
Shiva
Hi,
Try this...not an optimized solution but it suffice your problem.
data one;
input dose1 date dose2date updateddate;
informat date dose2date updateddate mmddyy10.;
format date dose2date updateddate date9.;
cards;
21 3/14/2010 4/4/2012 04/04/2012
23 3/4/2010 . 04/04/2012
62 3/4/2010 4/3/2012 04/04/2012
67 3/4/2010 4/3/2012 04/04/2012
98 3/4/2010 4/3/2012 04/04/2012
99 3/4/2010 . 04/04/2012
123 3/4/2010 4/3/2012 04/04/2012
;
run;
data two;
input dose1 date dose2date updateddate;
informat date dose2date updateddate mmddyy10.;
format date dose2date updateddate date9.;
cards;
21 3/14/2010 4/4/2012 04/04/2012
23 3/4/2010 4/8/2012 04/10/2012
62 3/4/2010 4/3/2012 04/04/2012
67 3/4/2010 4/3/2012 04/04/2012
98 3/4/2010 4/3/2012 04/04/2012
99 3/4/2010 4/8/2012 04/10/2012
123 3/4/2010 4/3/2012 04/04/2012
126 4/3/2011 4/9/2012 4/10/2012
;
run;
proc sort data=one;by dose1;run;
proc sort data=two(where=(updateddate='10APR2012'd));by dose1;run;
data testing;
merge one (in=a) two(in=b);
by dose1;
if b and not a;
run;
data one;
set one testing;
run;
proc sql ;
update one m
set dose2date = (select dose2date from two r
where m.dose1 = r.dose1
)
where m.dose1 in (select dose1 from two) ;
quit ;
Thanks,
Shiva
Hi Shiva,
Just a minor modification to your code. Hope this is correct. Please check.
data dataset1;
input dose1 date:mmddyy10. dose2date:mmddyy10. updateddate :mmddyy10.;
format date dose2date updateddate date9.;
cards;
21 3/14/2010 4/4/2012 04/04/2012
23 3/4/2010 . 04/04/2012
62 3/4/2010 4/3/2012 04/04/2012
67 3/4/2010 4/3/2012 04/04/2012
98 3/4/2010 4/3/2012 04/04/2012
99 3/4/2010 . 04/04/2012
123 3/4/2010 4/3/2012 04/04/12
;
run;
data dataset2;
input dose1 date:mmddyy10. dose2date:mmddyy10. updateddate :mmddyy10.;
format date dose2date updateddate date9.;
cards;
21 3/14/2010 4/4/2012 04/04/2012
23 3/4/2010 4/8/2012 04/10/2012
62 3/4/2010 4/3/2012 04/04/2012
67 3/4/2010 4/3/2012 04/04/2012
98 3/4/2010 4/3/2012 04/04/2012
99 3/4/2010 4/8/2012 04/10/2012
123 3/4/2010 4/3/2012 04/04/2012
126 4/3/2011 4/9/2012 4/10/2012
;
run;
proc sort data=dataset1;
by dose1;
run;
proc sort data=dataset2;
by dose1;
run;
data new;
update dataset1 dataset2;
by dose1;
run;
proc print data=new;
run;
can any body tell performace improvement here?
proc sort data=table1;
by id;
run;
proc sort data=table2;
by id;
run;
here table1 is too huge7M table2 is only 100k obs.
data table1;
update table1 table2;
by id;
run;
Q. the performance is very very poor by using above code. Any ideas to improve performance.?
Example data and code is as shown above.
I prefer to Hash Table or proc format cntlin= .
Answer here: https://communities.sas.com/message/124657#124657
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.