Help using Base SAS procedures

Proc Sql

Reply
Occasional Contributor
Posts: 17

Proc Sql

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

Occasional Contributor
Posts: 17

Proc sql

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

Super User
Posts: 5,257

Re: Proc sql

Why are you fixed to SQL?
This is row type query, whoch is not a good match for SQL. Try the data step instead.
Data never sleeps
Super User
Posts: 17,840

Re: Proc sql

 

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. 

Super User
Posts: 9,681

Re: Proc Sql

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;


Occasional Contributor
Posts: 17

Re: Proc Sql

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.
Super User
Posts: 17,840

Re: Proc Sql


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;

Ask a Question
Discussion stats
  • 6 replies
  • 338 views
  • 0 likes
  • 4 in conversation