help with sas code

[Augusto]

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

[JerryLeBreton]

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;

[Kurt_Bremser]

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;

[MadhuKorni]

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;

[slchen]

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;

[Ksharp · Accepted solution]

Code: Program

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;