topic Re: re: Split Rows in SAS Programming

re: Split Rows

twildone — Sun, 26 Mar 2023 20:17:13 GMT

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

Re: re: Split Rows

PaigeMiller — Sun, 26 Mar 2023 20:09:04 GMT

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".

Re: re: Split Rows

twildone — Sun, 26 Mar 2023 20:18:38 GMT

Hi Paige,

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

Re: re: Split Rows

PaigeMiller — Sun, 26 Mar 2023 20:58:06 GMT

So what happens if

"if the end_subperiod is greater than the end_subperiod1"

is NOT true?

Re: re: Split Rows

twildone — Sun, 26 Mar 2023 20:46:19 GMT

Hi,

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

Re: re: Split Rows

PaigeMiller — Sun, 26 Mar 2023 21:09:26 GMT

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).

Re: re: Split Rows

twildone — Sun, 26 Mar 2023 22:19:55 GMT

Thanks Paige...It worked!!