topic Appending data with dynamic month in SAS Procedures

Appending data with dynamic month

Newbee_MVK — Tue, 28 Feb 2012 11:30:13 GMT

data jan;
informat rundate mmddyy10.;
format rundate mmddyy10.;
input rundate product $ premthbal curmthbal;
cards;
01/05/2012 aaa . 1
01/05/2012 abc . 2
01/05/2012 bbb . 3
01/05/2012 ccc . 4
01/05/2012 ddd . 5
01/05/2012 eee . 6
01/05/2012 fff . 7
01/05/2012 ggg . 8
;
run;
data feb;
informat rundate mmddyy10.;
format rundate mmddyy10.;
input rundate product $ premthbal curmthbal;
cards;
02/05/2012 aaa . 9
02/05/2012 abc . 8
02/05/2012 bbb . 7
02/05/2012 ccc . 6
02/05/2012 ddd . 5
02/05/2012 eee . 4
02/05/2012 fff . 3
02/05/2012 ggg . 2
;
run;
data mar;
informat rundate mmddyy10.;
format rundate mmddyy10.;
input rundate product $ premthbal curmthbal;
cards;
03/05/2012 aaa . 1
03/05/2012 abc . 2
03/05/2012 bbb . 3
03/05/2012 ccc . 4
03/05/2012 ddd . 5
03/05/2012 eee . 6
03/05/2012 fff . 7
03/05/2012 ggg . 8
;
run;
data apr;
informat rundate mmddyy10.;
format rundate mmddyy10.;
input rundate product $ premthbal curmthbal;
cards;
04/05/2012 aaa . 3
04/05/2012 abc . 2
04/05/2012 bbb . 1
04/05/2012 ccc . 3
04/05/2012 ddd . 2
04/05/2012 eee . 1
04/05/2012 fff . 3
04/05/2012 ggg . 2
;
run;
data test(keep=product premthbal);
set jan;
premthbal=curmthbal;
run;
proc sort data=test;
by product;
run;
proc sort data=feb;
by product;
run;
data final;
merge feb test;
by product;
run;

data final1;
set jan final;
run;
data final2;
set final1 mar;
run;
data final3;
set final2 apr;
run;

Pls find the above datasets which has information about the current date as janunary and coming month as Febuary.
The situation now is i have to import the data from flat files and each month flatfile is generated with timestamp.
Every time i run the code it should have the latest month data and prior month should be appended to latest month data below.
My final1 in the above code is working fine. In the same way i want to create final2 & final3.

I want final2 output:
rundate    product premthbal curmthbal
01/05/2012   aaa       .        1
01/05/2012   abc       .        2
01/05/2012   bbb       .        3
01/05/2012   ccc       .        4
01/05/2012   ddd       .        5
01/05/2012   eee       .        6
01/05/2012   fff       .        7
01/05/2012   ggg       .        8
02/05/2012   aaa       1        9
02/05/2012   abc       2        8
02/05/2012   bbb       3        7
02/05/2012   ccc       4        6
02/05/2012   ddd       5        5
02/05/2012   eee       6        4
02/05/2012   fff       7        2
02/05/2012   ggg       8        1
03/05/2012   aaa       9        1
03/05/2012   abc       8        2
03/05/2012   bbb       7        3
03/05/2012   ccc       6        4
03/05/2012   ddd       5        5
03/05/2012   eee       4        6
03/05/2012   fff       3        7
03/05/2012   ggg       2        8

final3 output:
rundate    product premthbal curmthbal
01/05/2012   aaa       .        1
01/05/2012   abc       .        2
01/05/2012   bbb       .        3
01/05/2012   ccc       .        4
01/05/2012   ddd       .        5
01/05/2012   eee       .        6
01/05/2012   fff       .        7
01/05/2012   ggg       .        8
02/05/2012   aaa       1        9
02/05/2012   abc       2        8
02/05/2012   bbb       3        7
02/05/2012   ccc       4        6
02/05/2012   ddd       5        5
02/05/2012   eee       6        4
02/05/2012   fff       7        2
02/05/2012   ggg       8        1
03/05/2012   aaa       9        1
03/05/2012   abc       8        2
03/05/2012   bbb       7        3
03/05/2012   ccc       6        4
03/05/2012   ddd       5        5
03/05/2012   eee       4        6
03/05/2012   fff       3        7
03/05/2012   ggg       2        8
04/05/2012   aaa       1        3
04/05/2012   abc       2        2
04/05/2012   bbb       3        1
04/05/2012   ccc       4        3
04/05/2012   ddd       5        2
04/05/2012   eee       6        1
04/05/2012   fff       7        3
04/05/2012   ggg       8        2

Any help appreciated.

Appending data with dynamic month

art297 — Tue, 28 Feb 2012 13:19:07 GMT

You DON'T want to merge the files, you want to concatinate them.

One way is to use proc append. e.g.:

So, if you are starting with Jan then use;

data final;

set Jan feb mar; /*etc*/

run;

then, for each month (as it occurs) after that, use, e.g.:

proc append

base=final data=mar;

run;

Appending data with dynamic month

Hima — Tue, 28 Feb 2012 14:07:44 GMT

Sysparm function can be used. This can be done in batch mode.

http://www.sascommunity.org/wiki/images/0/01/SYSPARM_paper-Jackson.pdf

Appending data with dynamic month

Hima — Tue, 28 Feb 2012 18:00:04 GMT

** run this in Jan **;

data master;
set jan;
run;

** run this in the month of feb **;

%let want_month=2 ;
options mprint;
%macro month;
%let list=Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;
proc append base = master data = %scan(&list,&want_month);
run;
%mend month;
%month

** run this in the month of march**;

%let want_month=3 ;
options mprint;
%macro month;
%let list=Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;
proc append base = master data = %scan(&list,&want_month);
run;
%mend month;
%month

** run this in the month of april **;
%let want_month=4 ;
options mprint;
%macro month;
%let list=Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;
proc append base = master data = %scan(&list,&want_month);
run;
%mend month;
%month

At the end of april the output would be as follows:

rundate

product

premthbal

curmthbal

1/5/2012	aaa	.	1
1/5/2012	abc	.	2
1/5/2012	bbb	.	3
1/5/2012	ccc	.	4
1/5/2012	ddd	.	5
1/5/2012	eee	.	6
1/5/2012	fff	.	7
1/5/2012	ggg	.	8
2/5/2012	aaa	.	9
2/5/2012	abc	.	8
2/5/2012	bbb	.	7
2/5/2012	ccc	.	6
2/5/2012	ddd	.	5
2/5/2012	eee	.	4
2/5/2012	fff	.	3
2/5/2012	ggg	.	2
3/5/2012	aaa	.	1
3/5/2012	abc	.	2
3/5/2012	bbb	.	3
3/5/2012	ccc	.	4
3/5/2012	ddd	.	5
3/5/2012	eee	.	6
3/5/2012	fff	.	7
3/5/2012	ggg	.	8
4/5/2012	aaa	.	3
4/5/2012	abc	.	2
4/5/2012	bbb	.	1
4/5/2012	ccc	.	3
4/5/2012	ddd	.	2
4/5/2012	eee	.	1
4/5/2012	fff	.	3
4/5/2012	ggg	.	2