topic Re: Proc sql in SAS Procedures

Proc Sql

umesh1 — Fri, 16 Sep 2016 20:34:07 GMT

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

Proc sql

umesh1 — Fri, 16 Sep 2016 20:38:10 GMT

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

Re: Proc sql

LinusH — Fri, 16 Sep 2016 21:19:57 GMT

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

Re: Proc sql

Reeza — Fri, 16 Sep 2016 21:27:35 GMT

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.

Re: Proc Sql

Ksharp — Sat, 17 Sep 2016 03:16:28 GMT

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;

Re: Proc Sql

umesh1 — Sun, 18 Sep 2016 20:48:55 GMT

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.

Re: Proc Sql

Reeza — Sun, 18 Sep 2016 22:33:52 GMT

@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;