Hi everybody,
I need help with coding an expansion in my records but I don't know where to start from. So I have the data in the following format:
fyear anndat revdat forecast analyst
12/31/2000 01/31/2000 01/31/2000 10 a
12/31/2000 01/20/2000 02/10/2000 5 b
12/31/2000 01/20/2000 04/18/2000 4 c
I want to have the following table where each of the records is expanded for the number of periods (months) between anndat and revdat.
fyear anndat revdat forecast analyst transaction monthyear
12/31/2000 01/31/2000 01/31/2000 10 a 1 01/2000
12/31/2000 01/20/2000 02/10/2000 5 b 2 01/2000
12/31/2000 01/20/2000 02/10/2000 5 b 2 02/2000
12/31/2000 01/20/2000 04/18/2000 4 c 3 01/2000
12/31/2000 01/20/2000 04/18/2000 4 c 3 02/2000
12/31/2000 01/20/2000 04/18/2000 4 c 3 03/2000
12/31/2000 01/20/2000 04/18/2000 4 c 3 04/2000
Any help is appreciated at this point! Thanks a lot in advance
data have;
informat fyear anndat revdat mmddyy10.;
input fyear anndat revdat forecast analyst $;
cards;
12/31/2000 01/31/2000 01/31/2000 10 a
12/31/2000 01/20/2000 02/10/2000 5 b
12/31/2000 01/20/2000 04/18/2000 4 c
;
data want;
set have;
do _n_=1 to intck('month',anndat,revdat)+1;
monthyear=intnx('month',anndat,_n_-1);
output;
end;
format fyear anndat revdat mmddyy10. monthyear mmyys7. ;
proc print;run;
Obs fyear anndat revdat forecast analyst monthyear
1 12/31/2000 01/31/2000 01/31/2000 10 a 01/2000
2 12/31/2000 01/20/2000 02/10/2000 5 b 01/2000
3 12/31/2000 01/20/2000 02/10/2000 5 b 02/2000
4 12/31/2000 01/20/2000 04/18/2000 4 c 01/2000
5 12/31/2000 01/20/2000 04/18/2000 4 c 02/2000
6 12/31/2000 01/20/2000 04/18/2000 4 c 03/2000
7 12/31/2000 01/20/2000 04/18/2000 4 c 04/2000
data have;
informat fyear anndat revdat mmddyy10.;
input fyear anndat revdat forecast analyst $;
cards;
12/31/2000 01/31/2000 01/31/2000 10 a
12/31/2000 01/20/2000 02/10/2000 5 b
12/31/2000 01/20/2000 04/18/2000 4 c
;
data want;
set have;
do _n_=1 to intck('month',anndat,revdat)+1;
monthyear=intnx('month',anndat,_n_-1);
output;
end;
format fyear anndat revdat mmddyy10. monthyear mmyys7. ;
proc print;run;
Obs fyear anndat revdat forecast analyst monthyear
1 12/31/2000 01/31/2000 01/31/2000 10 a 01/2000
2 12/31/2000 01/20/2000 02/10/2000 5 b 01/2000
3 12/31/2000 01/20/2000 02/10/2000 5 b 02/2000
4 12/31/2000 01/20/2000 04/18/2000 4 c 01/2000
5 12/31/2000 01/20/2000 04/18/2000 4 c 02/2000
6 12/31/2000 01/20/2000 04/18/2000 4 c 03/2000
7 12/31/2000 01/20/2000 04/18/2000 4 c 04/2000
Hi Linlin,
Thanks a lot for the code! I really appreciate your help!
JP
It seems transaction should be 4 for the last group :
data have;
format fyear anndat revdat mmddyy10.;
input (fyear anndat revdat) (:mmddyy10.) forecast analyst $;
datalines;
12/31/2000 01/31/2000 01/31/2000 10 a
12/31/2000 01/20/2000 02/10/2000 5 b
12/31/2000 01/20/2000 04/18/2000 4 c
;
data want(drop=i);
set have;
transaction = intck("MONTH", anndat, revdat) + 1;
format monthYear MMYYS7.;
monthyear = anndat;
do i = 1 to transaction;
output;
monthyear = intnx("MONTH",monthyear,1,"SAME");
end;
run;
proc print; run;
PG
Hi PGStats,
Thank you very much for the prompt response. 'transaction' is not that important. I just put it there to show that each record is repeated for a certain number of periods. I see your point though, cos' I think you understood that transaction means the number of expanded records.
In any case the code works and I am grateful
JP
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.
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.