topic help with sas code in SAS Procedures

help with sas code

Augusto — Fri, 14 Aug 2015 02:39:07 GMT

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

Re: help with sas code

JerryLeBreton — Fri, 14 Aug 2015 07:13:27 GMT

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;

format dt_in dt_out earlier_dt yymmn6.;

drop i saved_dt_out;

run;

Re: help with sas code

Kurt_Bremser — Fri, 14 Aug 2015 08:12:02 GMT

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;

run;

Re: help with sas code

MadhuKorni — Fri, 14 Aug 2015 14:16:33 GMT

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;

Re: help with sas code

slchen — Fri, 14 Aug 2015 19:29:23 GMT

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;

Re: help with sas code

Ksharp — Sat, 15 Aug 2015 12:33:47 GMT

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;