re: Split Rows

[twildone]

Hi...I have got to the point where I need to split the row if the end_subperiod is greater than the end_subperiod1, and if so, then the start_subperiod entry of the new row will be the end_subperiod1 from the previous row. Would I need to use Retain? Thanks.

data Have;
    length season $ 6 start_subperiod $ 10 end_subperiod $ 10 end_subperiod1 $ 10;
    format season $char6. start_subperiod $char10. end_subperiod $char10. end_subperiod1 $char10.;
    informat season $char6. start_subperiod $char10. end_subperiod $char10. end_subperiod1 $char10.;
    infile datalines4 dlm='7F'x missover dsd;
    input season : $char6. start_subperiod : $char10. end_subperiod : $char10. end_subperiod1 : $char10.;
datalines4;
Fall2016-09-082016-10-262016-10-21
Fall2016-09-082016-11-232016-10-21
Summer2016-07-022016-08-252016-08-19
Summer2016-07-022016-08-312016-08-19
Winter2016-01-022016-03-312016-02-19
Winter2017-01-022017-03-152017-02-17
;;;;

Want:

season	start_subperiod	end_subperiod	end_subperiod1
Fall	2016-09-08	2016-10-26	2016-10-21
Fall	2016-10-21	2016-10-26	2016-10-26
Fall	2016-09-08	2016-11-23	2016-10-21
Fall	2016-10-21	2016-11-23	2016-11-23
Summer	2016-07-02	2016-08-25	2016-08-19
Summer	2016-08-19	2016-08-25	2016-08-25
Summer	2016-07-02	2016-08-31	2016-08-19
Summer	2016-08-19	2016-08-31	2016-08-31
Winter	2016-01-02	2016-03-31	2016-02-19
Winter	2016-02-19	2016-03-31	2016-03-31
Winter	2017-01-02	2017-03-15	2017-02-17
Winter	2017-02-17	2017-03-15	2017-03-15

[PaigeMiller]

Why does line 3 get split? It does not meet the stated criterion "if the end_subperiod is greater than the end_subperiod1".

Why does line 6 get split? It does not meet the stated criterion "if the end_subperiod is greater than the end_subperiod1".

[twildone]

Hi Paige,

Sorry about that.....I made the corrections. Thanks

[PaigeMiller]

So what happens if

"if the end_subperiod is greater than the end_subperiod1"

is NOT true?

[twildone]

Hi,

If the end_subperiod is less than the end_subperiod1 then the row is not split and the row is outputted as is

[PaigeMiller · Accepted solution]

data want;
    set have;
    if end_subperiod>end_subperiod1 then do;
        output;
        start_subperiod=end_subperiod1;
        end_subperiod1=end_subperiod;
        output;
    end;
    else output;
run;

Ideally, from now on, you should work with dates as numbers rather than character strings (although in this case, it might not matter, in most cases working with dates as numbers has many advantages).

[twildone]

Thanks Paige...It worked!!