Help using Base SAS procedures

Proc append?

Reply
Frequent Contributor
Posts: 110

Proc append?

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

Super Contributor
Posts: 349

Re: Proc append?

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

Super Contributor
Posts: 349

Re: Proc append?

Hi,

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

Thanks,

Shiva

Super Contributor
Posts: 349

Re: Proc append?

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

Trusted Advisor
Posts: 1,137

Re: Proc append?

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
Frequent Contributor
Posts: 110

Re: Proc append?

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.

Super User
Posts: 10,044

Re: Proc append?

I prefer to Hash Table or proc format cntlin=  .

Frequent Contributor
Posts: 110

Re: Proc append?

Respected Advisor
Posts: 4,173

Re: Proc append?

Ask a Question
Discussion stats
  • 8 replies
  • 550 views
  • 3 likes
  • 5 in conversation