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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.