topic Re: Calculated columns with subqueries in New SAS User

Calculated columns with subqueries

cmemtsa — Tue, 29 Jan 2019 09:47:09 GMT

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.

Re: Calculated columns with subqueries

PeterClemmensen — Tue, 29 Jan 2019 10:01:03 GMT

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;

Re: Calculated columns with subqueries

cmemtsa — Tue, 29 Jan 2019 10:03:52 GMT

By chance there's that sequence. I want to calculate columns (extract data) only for CF that meet the criteria.

Re: Calculated columns with subqueries

PeterClemmensen — Tue, 29 Jan 2019 10:06:00 GMT

Ok. And you want to do so with PROC SQL or is a data step acceptable?

Re: Calculated columns with subqueries

PeterClemmensen — Tue, 29 Jan 2019 10:22:29 GMT

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;

Re: Calculated columns with subqueries

cmemtsa — Tue, 29 Jan 2019 10:13:00 GMT

This is my code and it's not working as it is.

Re: Calculated columns with subqueries

PeterClemmensen — Tue, 29 Jan 2019 10:22:47 GMT

You are right. I changed it.

Re: Calculated columns with subqueries

cmemtsa — Tue, 29 Jan 2019 10:35:42 GMT

Thank you a lot!

Re: Calculated columns with subqueries

PeterClemmensen — Tue, 29 Jan 2019 10:42:26 GMT

Anytime 🙂 Glad we found the right answer.