Calculated columns with subqueries

[cmemtsa]

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.

[PeterClemmensen]

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;

[cmemtsa]

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

[PeterClemmensen]

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

[PeterClemmensen · Accepted solution]

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;

[cmemtsa]

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

[PeterClemmensen]

You are right. I changed it.

[cmemtsa]

Thank you a lot!

[PeterClemmensen]

Anytime 🙂 Glad we found the right answer.