topic Re: Proc append? in SAS Procedures

Proc append?

sassharp — Thu, 19 Apr 2012 03:41:58 GMT

I need to append new data to historical data on a daily bases.

my question here is?

historical data on rdbms side

table1

dose1 date

dose2date

updated date

3/14/2010

4/4/2012

04/04/2012

3/4/2010

04/04/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

04/04/2012

123

3/4/2010

4/3/2012

04/04/2012

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

table 2

dose1 date

dose2date

updated date

3/14/2010

4/4/2012

04/04/2012

3/4/2010

4/8/2012

04/10/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

4/3/2012

04/04/2012

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

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.

dose1 date

dose2date

updated date

3/14/2010

4/4/2012

04/04/2012

3/4/2010

04/04/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

4/3/2012

04/04/2012

3/4/2010

04/04/2012

123

3/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	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

Re: Proc append?

shivas — Thu, 19 Apr 2012 04:16:01 GMT

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

Re: Proc append?

shivas — Thu, 19 Apr 2012 04:22:39 GMT

Hi,

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

Thanks,

Shiva

Re: Proc append?

shivas — Thu, 19 Apr 2012 05:06:00 GMT

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

Re: Proc append?

sassharp — Wed, 25 Apr 2012 03:06:26 GMT

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.

Re: Proc append?

Ksharp — Wed, 25 Apr 2012 05:35:55 GMT

I prefer to Hash Table or proc format cntlin= .

Re: Proc append?

sassharp — Wed, 25 Apr 2012 12:31:30 GMT

https://communities.sas.com/thread/34735

Re: Proc append?

Patrick — Thu, 26 Apr 2012 04:45:24 GMT

Answer here: https://communities.sas.com/message/124657#124657

Re: Proc append?

Jagadishkatam — Thu, 26 Apr 2012 05:22:43 GMT

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;