BookmarkSubscribeRSS Feed
sassharp
Calcite | Level 5

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
dose2dateupdated date
213/14/20104/4/201204/04/2012
233/4/201004/04/2012
623/4/20104/3/201204/04/2012
673/4/2010
4/3/2012
04/04/2012
983/4/2010
4/3/2012
04/04/2012
993/4/201004/04/2012
1233/4/2010
4/3/2012
04/04/2012

but on 4/10/2012 the table changes as follows on rdbms side

table 2

Id
dose1 date
dose2dateupdated date
213/14/20104/4/201204/04/2012
233/4/20104/8/201204/10/2012
623/4/20104/3/201204/04/2012
673/4/2010
4/3/2012
04/04/2012
983/4/2010
4/3/2012
04/04/2012
993/4/20104/8/201204/10/2012
1233/4/2010
4/3/2012
04/04/2012
1264/3/20114/9/20124/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
dose2dateupdated date
213/14/20104/4/201204/04/2012
233/4/201004/04/2012
623/4/20104/3/201204/04/2012
673/4/2010
4/3/2012
04/04/2012
983/4/2010
4/3/2012
04/04/2012
993/4/201004/04/2012
1233/4/2010
4/3/2012
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
dose2dateupdated date
233/4/20104/8/201204/10/2012
993/4/20104/8/201204/10/2012
1264/3/20114/9/20124/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

8 REPLIES 8
shivas
Pyrite | Level 9

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

shivas
Pyrite | Level 9

Hi,

Sorry it updates complete row(updatecolumn will also get updated) ....above solution will not work for this scenario.

Thanks,

Shiva

shivas
Pyrite | Level 9

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

Jagadishkatam
Amethyst | Level 16

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;

Thanks,
Jag
sassharp
Calcite | Level 5

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.

Ksharp
Super User

I prefer to Hash Table or proc format cntlin=  .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1249 views
  • 3 likes
  • 5 in conversation