Hello,
Firstly, I would like o thanks all of you that have take a time to try to help me for this "job".
Well, I'm needing to generate a code to output one line for each month, to dt_in until dt_out.
HAVE
cod dt_in dt_out flg_out
1 201402 201406 1
1 201407 201409 1
2 201501 201503 1
2 201504 0
-------------------------------------------------------------
WANT
cod dt_in dt_out flg_out earlier_dt
1 201402 0
1 201402 0 201402
1 201402 0 201403
1 201402 0 201404
1 201402 201406 1 201405
1 201407 0
1 201407 0 201407
1 201407 201409 1 201408
2 201501 0
2 201501 0 201501
2 201501 201503 1 201502
2 201504 0
2 201504 0 201504
2 201504 0 201505
2 201504 0 201506
2 201504 0 201507
Please, consider until this month
The trick are.
1) The flg_out must have the value 0 until achieve the last line date. Wich do not have dt_out filled, the flg_out will continue = 0
2) The dt_out will be filled only for the last line date when have the dt_out
3) Create a new field called earlier_dt that should be filled with the previous date.
Thanks a lot
data have;
input cod dt_in dt_out flg_out;
informat dt_in dt_out yymmn6.;
format dt_in dt_out yymmn6.;
cards;
1 201402 201406 1
1 201407 201409 1
2 201501 201503 1
2 201504 . 0
;
run;
data want;
set have;
flag=0;_dt_out=.;
earlier_dt=.;output;
n=intck('month',dt_in,coalesce(dt_out,today()));
do i=0 to n-1;
earlier_dt=intnx('month',dt_in,i);
if i=n-1 then do;flag=flg_out;_dt_out=dt_out ;end;
output;
end;
format earlier_dt _dt_out yymmn6.;
drop i n flg_out dt_out ;
run;
Here's a solution. Doesn't look very pretty but seems to do the job.
data have;
infile cards missover;
input cod dt_in :yymmn6. dt_out :yymmn6. flg_out;
cards;
1 201402 201406 1
1 201407 201409 1
2 201501 201503 1
2 201504 . 0
3 201503 201503 1
;
run;
data want ;
set have;
do i= 0 to 99 ;
if i=0 then
do;
earlier_dt = .;
saved_dt_out = dt_out;
end;
else
earlier_dt = intnx('month',dt_in, i-1, 'b');
if saved_dt_out = intnx('month',dt_in, i, 'b') then
do;
dt_out = saved_dt_out;
flg_out = 1;
output;
leave;
end;
else
do;
dt_out = .;
flg_out = 0;
output;
if saved_dt_out = . and intnx('month',dt_in, i, 'b') > today() then
leave;
end;
end;
format dt_in dt_out earlier_dt yymmn6.;
drop i saved_dt_out;
run;
data have;
input cod dt_in dt_out flg_out;
informat dt_in dt_out yymmn6.;
format dt_in dt_out yymmn6.;
cards;
1 201402 201406 1
1 201407 201409 1
2 201501 201503 1
2 201504 . 0
;
run;
%let curr_month='01jul2015'd;
data want (keep=cod dt_in dt_out flg_out earlier_dt);
set have (rename=(dt_out=dt_outold));
format dt_out earlier_dt yymmn6.;
format check_date ddmmyyp10.;
flg_out = 0;
output; * creates the first record;
if dt_outold ne .
then do;
earlier_dt = dt_in;
do until (earlier_dt > intnx('month',dt_outold,-2,'begin'));
output;
earlier_dt = intnx('month',earlier_dt,1,'begin');
check_date = earlier_dt;
end;
flg_out = 1;
earlier_dt = intnx('month',dt_outold,-1,'begin');
dt_out = dt_outold;
output;
end;
else do;
earlier_dt = dt_in;
do until (earlier_dt > &curr_month);
output;
earlier_dt = intnx('month',earlier_dt,1,'begin');
end;
end;
run;
data have;
infile cards missover;
input cod dt_in :yymmn6. dt_out :yymmn6. flg_out;
format dt_in dt_out yymmn6.;
cards;
1 201402 201406 1
1 201407 201409 1
2 201501 201503 1
2 201504 . 0
;
data want;
set have;
format Earlier_dt dtout yymmn6.;
by dt_in notsorted;
if first.dt_in then
do;
Earlier_dt= .;flg_out=0;dtout=.;output;
Earlier_dt=dt_in;
end;
if dt_out ne . then
do while(Earlier_dt lt dt_out);
if Earlier_dt = intnx('Month',dt_out,-1) then do;dtout = dt_out;flg_out=1;end;
output;
Earlier_dt = intnx('Month',Earlier_dt,1);
end;
else
*As the logic is not mentioned for missing values in Dt_Out and basing on the output
the loop is iterated for 3 times to get 201507;
do while (cnt le 3);
output;
Earlier_dt = intnx('Month',Earlier_dt,1);
cnt+1;
end;
drop dt_out cnt;
rename dtout = dt_out;
run;
options missing = " ";
proc print ;
var cod dt_in dt_out flg_out earlier_dt;
run;
data want(rename=(_dt_out=dt_out _flg_out=flg_out));
set have;
if not missing (dt_out) then month=intck('month',dt_in,dt_out);
else month=intck('month',dt_in,today());
do i=1 to month;
dt_in=dt_in;
_dt_out=. ;
_flg_out=0;
if i^=1 then earlier_dt=intnx('month',dt_in,i-2);
output;
end;
earlier_dt=earlier_dt+1;
_dt_out=dt_out;
_flg_out=flg_out;
earlier_dt=intnx('month',earlier_dt,1);
output;
format dt_in dt_out _dt_out earlier_dt yymmn6.;
drop flg_out dt_out i month;
run;
data have;
input cod dt_in dt_out flg_out;
informat dt_in dt_out yymmn6.;
format dt_in dt_out yymmn6.;
cards;
1 201402 201406 1
1 201407 201409 1
2 201501 201503 1
2 201504 . 0
;
run;
data want;
set have;
flag=0;_dt_out=.;
earlier_dt=.;output;
n=intck('month',dt_in,coalesce(dt_out,today()));
do i=0 to n-1;
earlier_dt=intnx('month',dt_in,i);
if i=n-1 then do;flag=flg_out;_dt_out=dt_out ;end;
output;
end;
format earlier_dt _dt_out yymmn6.;
drop i n flg_out dt_out ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.