SAS: using group by in proc sql doesn't separate out instances chronologically
<p>Consider the following SAS code:</p>

<pre><code>data test;
    format dt date9.
           ctry_cd $2.
           sn $2.;
    input ctry_cd sn dt;
    datalines;
    US 1 20000
    US 1 20001
    US 1 20002
    CA 1 20003
    CA 1 20004
    US 1 20005
    US 1 20006
    US 1 20007
    ES 2 20001
    ES 2 20002
    ;
run;

proc sql;
    create table check as
    select
        sn,
        ctry_cd,
        min(dt) as begin_dt format date9.,
        max(dt) as end_dt format date9.
    from test
    group by sn, ctry_cd;
quit;
</code></pre>

<p>This returns:</p>

<pre><code>1 CA 07OCT2014 08OCT2014
1 US 04OCT2014 11OCT2014
2 ES 05OCT2014 06OCT2014
</code></pre>

<p>I would like for the <code>proc sql</code> distinguish between the country moves; that is, return</p>

<pre><code>1 US 04OCT2014 06OCT2014
1 CA 07OCT2014 08OCT2014
1 US 09OCT2014 11OCT2014
2 ES 05OCT2014 06OCT2014 
</code></pre>

<p>So it still groups the instances by sn and ctry_nm but pays attention to the date so I have a timeline.</p>