SAS SQL: Grouping within groups

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

SAS SQL: Grouping within groups

I have the data in Exhibit 1 of the attachment which I get by running the query shown below. However, what I am looking to do is to get my data to look like Exhibit 2 of the attached so that I only retain the records with the later Mod_Date for every Loan_ID by setting row1=1. Can anyone please offer their input? Any help would be extremely appreciated. Many thanks in advance.
 

proc sql;

create table Exhibit2 as

select  t.Loan_ID, t.Mod_Option, t.Mod_Date, t.Loan_UPB, t.row1,
            row_number() over(partition by Mod_Option) as row2
from(select  Loan_ID, Mod_Option, Mod_Date, Loan_UPB,
            row_number() over(partition by Loan_ID order by a.Mod_Date) as row1
           from Exhibit1) t;

quit;


Accepted Solutions
Solution
‎10-19-2016 11:53 PM
Trusted Advisor
Posts: 1,558

Re: SAS SQL: Grouping within groups

Posted in reply to maroulator

After creating exhibit1, row2 already exists.

To create exhibit2 do:

 

proc sql;

create table Exhibit2 as

select  t.Loan_ID, t.Mod_Option, t.Mod_Date, t.Loan_UPB,
           min(row2) as row1
from Exhibit1 t;

quit;

 

View solution in original post


All Replies
Super User
Posts: 19,787

Re: SAS SQL: Grouping within groups

Posted in reply to maroulator

That's not SAS code. 

Are you using SQL pass through? Can you use a data step instead?

 

Please post your data in the forum rather than an attachment and highlight the differences. 

Frequent Contributor
Posts: 122

Re: SAS SQL: Grouping within groups

[ Edited ]

I am using a passthrough. If you can solve this using a data step, by all means. My data currently looks like Exhibit1 and I want it to look like Exhibit2; the goal is to retain the records with the most recent Mod_Date per Loan_ID from Exhibit1 to end up with what I have in Exhibit2.

 

Thanks in advance.

 

Exhibit 1:

Loan_ID Mod_Option    Mod_Date     Loan_UPB   row1  row2

1341               3                  10/2/2011       123,876       1          1

1341               3                  10/2/2011       123,876       2          2

1341               3                  10/2/2011       123,531       3          3

1341               3                  10/2/2011       123,531       4          4

1341               4                     9/2/2011      123,355       5          1

1341               4                    9/2/2011      123,355       6          2

3211               2                     3/4/2014      115,780       1          1

3211               2                     3/4/2014      115,780       2          2

3211               2                     3/4/2014      115,780       3          3

3211               1                     2/3/2014      115,520       4          1

3211               1                     2/3/2014      115,520       5          2

 

Exhibit 2:

Loan_ID Mod_Option    Mod_Date     Loan_UPB   row1 

1341               3                  10/2/2011       123,876       1         

1341               3                  10/2/2011       123,876       1         

1341               3                  10/2/2011       123,531       1         

1341               3                  10/2/2011       123,531       1         

1341               4                     9/2/2011      123,355       2         

1341               4                     9/2/2011      123,355       2         

3211              2                     3/4/2014      115,780       1         

3211               2                     3/4/2014      115,780       1         

3211               2                     3/4/2014      115,780       1         

3211               1                    2/3/2014      115,520       2         

3211               1                     2/3/2014      115,520       2         

 

Super User
Posts: 10,023

Re: SAS SQL: Grouping within groups

Posted in reply to maroulator

Assuming I understand what you mean.

 

data have;
input (Loan_ID Mod_Option    Mod_Date     Loan_UPB   row1  row2) (:$20.);
cards;
1341               3                  10/2/2011       123,876       1          1
1341               3                  10/2/2011       123,876       2          2
1341               3                  10/2/2011       123,531       3          3
1341               3                  10/2/2011       123,531       4          4
1341               4                     9/2/2011      123,355       5          1
1341               4                    9/2/2011      123,355       6          2
3211               2                     3/4/2014      115,780       1          1
3211               2                     3/4/2014      115,780       2          2
3211               2                     3/4/2014      115,780       3          3
3211               1                     2/3/2014      115,520       4          1
3211               1                     2/3/2014      115,520       5          2
;
run;
data want;
 set have;
 by loan_id mod_date notsorted;
 if first.loan_id then want=0;
 want+first.mod_date;
run;
Respected Advisor
Posts: 4,173

Re: SAS SQL: Grouping within groups

[ Edited ]
Posted in reply to maroulator

The SQL you've posted looks like Oracle to me.

 

If you want to do it via SQL pass-through then you need to get your Oracle SQL syntax right. You then also need to pass the native Oracle syntax correctly to Oracle (using a connect statement).

 

Below working code tested with your sample data.

proc sql;
  connect to oracle as con1
  (<connection parameters for your database>);

  create table want as
  select 
    Loan_ID, 
    Mod_Option, 
    datepart(Mod_Date) as Mod_Date format=mmddyy10., 
    Loan_UPB format=comma20.,
    row1
  from connection to con1
    (
      select  
        Loan_ID, 
        Mod_Option, 
        Mod_Date, 
        Loan_UPB,
        dense_rank() over(partition by LOAN_ID order by trunc(mod_date) DESC) as row1
      from exhibit1
  		)
    ;

  disconnect from con1;
quit;

Result:

Capture.PNG

 

And as @Ksharp suggested you can get the same result easily via a SAS data step.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

data want;
set ora_lib.exhibit1;
by loan_id DESCENDING mod_date;
format mod_date mmddyy10. Loan_UPB comma20.;
mod_date=datepart(mod_date);

if first.loan_id then
want=0;
want+first.mod_date;
run;

Just make sure the "mod_date" in Oracle is always aligned to the beginning of the day as else some additional coding would be required to get the same result (I've handled this in the Oracle SQL above via the trunc() function).

Solution
‎10-19-2016 11:53 PM
Trusted Advisor
Posts: 1,558

Re: SAS SQL: Grouping within groups

Posted in reply to maroulator

After creating exhibit1, row2 already exists.

To create exhibit2 do:

 

proc sql;

create table Exhibit2 as

select  t.Loan_ID, t.Mod_Option, t.Mod_Date, t.Loan_UPB,
           min(row2) as row1
from Exhibit1 t;

quit;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 465 views
  • 1 like
  • 5 in conversation