Hi,
I have this dataset
DATE CD AMOUNT P N
31-Oct-18 A1 5 1 .
31-Oct-18 A2 10 1 1
31-Oct-18 A3 15 . 1
30-Nov-18 A1 20 1 .
30-Nov-18 A2 25 1 1
30-Nov-18 A3 30 . 1
31-Dec-18 A1 35 1 .
31-Dec-18 A2 40 1 1
31-Dec-18 A3 45 . 1
and I wrote this code
proc sql ;
create table want as
select DATE, CD,
(SELECT AMOUNT
FROM have B WHERE P=1 AND N IS NULL AND A.DATE=B.DATE) AS C,
(SELECT AMOUNT
FROM have B WHERE P IS NULL AND N=1 AND A.DATE=B.DATE) AS D,
FROM HAVE A;
quit;
to get this table
DATE CD C D
31-Oct-18 A1 5 .
31-Oct-18 A3 . 15
30-Nov-18 A1 20 .
30-Nov-18 A3 . 30
31-Dec-18 A1 35 .
31-Dec-18 A3 . 45
but it's not working.
Could you please help me?
Thanks.
ok, here is a solution
data want;
set have;
if p=1 and n=. then c=amount;
if p=. and n=1 then d=amount;
if (p=1 and n=.) | (p=. and n=1);
keep date cd c d;
run;
What is the logic here? It seems to me like you want c to be the first amount value for each date and d to be the last amount value for each date?
In that case do
data have;
input date:date9. cd $ amount p n;
format date date9.;
datalines;
31-oct-18 a1 5 1 .
31-oct-18 a2 10 1 1
31-oct-18 a3 15 . 1
30-nov-18 a1 20 1 .
30-nov-18 a2 25 1 1
30-nov-18 a3 30 . 1
31-dec-18 a1 35 1 .
31-dec-18 a2 40 1 1
31-dec-18 a3 45 . 1
;
data want;
set have;
by date;
if first.date then c=amount;
if last.date then d=amount;
if first.date or last.date;
keep date cd c d;
run;
By chance there's that sequence. I want to calculate columns (extract data) only for CF that meet the criteria.
Ok. And you want to do so with PROC SQL or is a data step acceptable?
ok, here is a solution
data want;
set have;
if p=1 and n=. then c=amount;
if p=. and n=1 then d=amount;
if (p=1 and n=.) | (p=. and n=1);
keep date cd c d;
run;
This is my code and it's not working as it is.
You are right. I changed it.
Thank you a lot!
Anytime 🙂 Glad we found the right answer.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.