Proc Sql

[umesh1]

Hi All,

I have data like this in the database

data test;

informat date date9.;

input Account $ Score Date;

format date date9.;

cards;

A1 0 30-Apr-15

A1 0 31-May-15

A1 0 30-Jun-15

A1 1 31-Jul-15

A1 2 31-Aug-15

A1 3 30-Sep-15

A1 4 31-Oct-15

A1 5 30-Nov-15

A2 0 31-Jan-14

A2 0 28-Feb-14

A2 2 31-Mar-14

A2 3 30-Apr-14

A2 4 31-May-14

A2 5 30-Jun-14

A3 1 28-Feb-13

A3 2 31-Mar-13

A3 3 30-Apr-13

A4 0 31-Jan-16

A4 0 29-Feb-16

A4 0 31-Mar-16

A4 0 30-Apr-16

A4 3 31-May-16

;

run;

and I wanted to pull only first record when score > 0

Resuling output looks like below

Account Score Date

A1 1 31-Jul-15

A2 2 31-Mar-14

A3 1 28-Feb-13

A4 3 31-May-16

When I pull the data I need to pull whole history of the account and after I need to get first record.

Thank you in advance

[umesh1]

Hi All,

I have the data like below in the database. I just need to pull the first record when score is greater than zero.

data test;

informat date date9.;

input Account $ Score Date;

format date date9.;

cards;

A1 0 30-Apr-15

A1 0 31-May-15

A1 0 30-Jun-15

A1 1 31-Jul-15

A1 2 31-Aug-15

A1 3 30-Sep-15

A1 4 31-Oct-15

A1 5 30-Nov-15

A2 0 31-Jan-14

A2 0 28-Feb-14

A2 2 31-Mar-14

A2 3 30-Apr-14

A2 4 31-May-14

A2 5 30-Jun-14

A3 1 28-Feb-13

A3 2 31-Mar-13

A3 3 30-Apr-13

A4 0 31-Jan-16

A4 0 29-Feb-16

A4 0 31-Mar-16

A4 0 30-Apr-16

A4 3 31-May-16

;

run;

resulting data looks like below

Account Score Date

A1 1 31-Jul-15

A2 2 31-Mar-14

A3 1 28-Feb-13

A4 3 31-May-16

when I pull from the database I need to pull entire history of the acccount and after that I need to filter out. Is there any way to tell proc sql to stop at the score greater than zero.

Thank you in advance

[LinusH]

Why are you fixed to SQL?
This is row type query, whoch is not a good match for SQL. Try the data step instead.

[Reeza]

proc sort data=have;
by account date;
run;

data want;
set have;
where score ne 0;
by account date;
if first.account;
run;

Much, much easier in a data step. 

[Ksharp]

It is not good for SQL.

data test;
informat date date9.;
input Account $ Score Date;
format date date9.;
cards;
A1 0 30-Apr-15
A1 0 31-May-15
A1 0 30-Jun-15
A1 1 31-Jul-15
A1 2 31-Aug-15
A1 3 30-Sep-15
A1 4 31-Oct-15
A1 5 30-Nov-15
A2 0 31-Jan-14
A2 0 28-Feb-14
A2 2 31-Mar-14
A2 3 30-Apr-14
A2 4 31-May-14
A2 5 30-Jun-14
A3 1 28-Feb-13
A3 2 31-Mar-13
A3 3 30-Apr-13
A4 0 31-Jan-16
A4 0 29-Feb-16
A4 0 31-Mar-16
A4 0 30-Apr-16
A4 3 31-May-16
;
run;
data want;
 set test;
 by account;
 retain found 0;
 if first.account then found=0;
 if score gt 0 and not found then do;output;found=1;end;
 drop found;
run;

[umesh1]

Hi

I cannot use the data step while I am pulling from the data base because I need to pull another variables together.

Guide me please.

[Reeza]

---

@umesh1 wrote:
Hi

I cannot use the data step while I am pulling from the data base because I need to pull another variables together.

Guide me please.

---

Thats not clear. 

This will work:

Proc sql;

select *

from have

group by account

where score >0 and date=Min(date);

quit;