Help using Base SAS procedures

Appending data with dynamic month

Reply
Occasional Contributor
Posts: 5

Appending data with dynamic month

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.

PROC Star
Posts: 7,468

Appending data with dynamic month

Posted in reply to Newbee_MVK

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;

Regular Contributor
Posts: 233

Appending data with dynamic month

Posted in reply to Newbee_MVK

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

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

Regular Contributor
Posts: 233

Appending data with dynamic month

Posted in reply to Newbee_MVK

** 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:

rundateproductpremthbalcurmthbal
1/5/2012aaa.1
1/5/2012abc.2
1/5/2012bbb.3
1/5/2012ccc.4
1/5/2012ddd.5
1/5/2012eee.6
1/5/2012fff.7
1/5/2012ggg.8
2/5/2012aaa.9
2/5/2012abc.8
2/5/2012bbb.7
2/5/2012ccc.6
2/5/2012ddd.5
2/5/2012eee.4
2/5/2012fff.3
2/5/2012ggg.2
3/5/2012aaa.1
3/5/2012abc.2
3/5/2012bbb.3
3/5/2012ccc.4
3/5/2012ddd.5
3/5/2012eee.6
3/5/2012fff.7
3/5/2012ggg.8
4/5/2012aaa.3
4/5/2012abc.2
4/5/2012bbb.1
4/5/2012ccc.3
4/5/2012ddd.2
4/5/2012eee.1
4/5/2012fff.3
4/5/2012ggg.2
Ask a Question
Discussion stats
  • 3 replies
  • 120 views
  • 0 likes
  • 3 in conversation