## help with sas code

Solved
Frequent Contributor
Posts: 81

# help with sas code

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

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

Accepted Solutions
Solution
‎08-15-2015 08:33 AM
Super User
Posts: 10,770

## Re: help with sas code

### 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 11 201407 201409 12 201501 201503 12 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;`

All Replies
Frequent Contributor
Posts: 88

## Re: help with sas code

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;

Super User
Posts: 10,215

## Re: help with sas code

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 77

## Re: help with sas code

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;

Super Contributor
Posts: 275

## Re: help with sas code

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;

Solution
‎08-15-2015 08:33 AM
Super User
Posts: 10,770

## Re: help with sas code

### 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 11 201407 201409 12 201501 201503 12 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;`
🔒 This topic is solved and locked.