Please Help Computing the Values using Data step or proc sql

[Dipu]

Hello Experts, back to you again,

please guide

I have four variables as shown below in the table 'Items'-

ID          E_Flag          tdate                    Center      

12          1                  12Jun2012             1           

12          1                  15Aug2012            2          

25          1                   25May2012           1

25          1                   27Jun2012            2

25          1                   12Jul2012             3   

25          1                   28Aug2012           4

25          1                   21Sep2012           5

31          1                   02Jul2012             1

31          1                   08Aug2012           2   

31          1                   02Sep2012           3

31          1                   22Octl2012           4

I need the out put as shown below (I am creating the datasets/tables in SAS)

Condition is-

for Id =12

when E_flag=1 and Center=1 then first_date= "First date of " tdate

when E_flag=1 and Center=2 then second_date= "second date of  "  tdate

when E_flag=1 and Center=Max of that no (i.e. 2 or 5 or 4 )then last_date= "last date of " tdate that is tdate value corresponding to (2 or 5 or 4)

Same thing for all the id's Please see the output dataset-

Out put data set-

ID          E_Flag              Center          First_date                   Second_date               Last_date

12          1                          2                12Jun2012                  .                                15Aug2012

25          1                          5               25May2012                  27Jun2012                  21Sep2012

25          1                          4               02Jul2012                    08Aug2012                 22Octl2012

Thank you

Dipu

[ballardw]

Is TDATE a string value or a SAS date value?

What happens if E_flag has a value other than 1?

[Dipu]

Hello First of all thank you,

tdate is the date value

numeric date9.

E_flag may have value 0

but one more variable is their called N_flag.

so if e_flag is 0 then N_flag is 1 and vice versa

[Reeza]

Can you post what happens when you have E_FLag=0 and N_Flag=1, because the solution for above with only E_FLag is easy enough. 

[Dipu]

Thanks reeza-

if E_flag =0 and N_Flag=1 then it should process the same query for N_Flag as we need for E_Flag

[Reeza]

This will match your output above, but I have my suspicions about it being the correct answer

proc sql;

    create table want as

    select max(ID) as ID, min(E_Flag) as E_Flag, max(Center) as Center,

                         min(tdate) as first_date format=date9., 

                        case when max(center)>2 then sum((center=2)*tdate)

                            else . end as second_date format=date9.,

                        max(tdate) as last_date format=date9.

    from have

    where E_Flag=1

    group by ID

    order by id;

quit;

[Dipu]

Thank you Reeza,

it is showing some syntax error

          ,case when max(center)>2 then sum((center=2)*tdate else . end as

                                                                        ----       --

                                                                        22         76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /,

              <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE,

              LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syn

I am trying to work on it...

[Reeza]

This assumes that Center and Tdate are both numeric.

[Dipu]

Right, both are numeric Reeza.

But I did not get the results.

:smileyplain:

[Dipu]

@Reeza

Is this the correct syntax?

case when max(center)>2 then sum((center=2)*tdate) else . end as second_date format=date9.,

it is showing error here.

[Reeza · Accepted solution]

1. Does the code below work as expected?

2. Post the full complete log.

data have;

informat tdate date9.;

format tdate date9.;

input ID          E_Flag          tdate                    Center  ;

cards;

12          1                  12Jun2012             1          

12          1                  15Aug2012            2         

25          1                   25May2012           1

25          1                   27Jun2012            2

25          1                   12Jul2012             3  

25          1                   28Aug2012           4

25          1                   21Sep2012           5

31          1                   02Jul2012             1

31          1                   08Aug2012           2  

31          1                   02Sep2012           3

31          1                   22Oct2012           4

;

run;

proc sql;

    create table want as

    select max(ID) as ID, min(E_Flag) as E_Flag, max(Center) as Center, min(tdate) as first_date format=date9., 

                        case when max(center)>2 then sum((center=2)*tdate)

                            else . end as second_date format=date9.,

                        max(tdate) as last_date format=date9.

    from have

    where E_Flag=1

    group by ID

    order by id;

quit;

[Dipu]

You are awesome Reeza....

In the previous code there was minor syntax error, sorry for troubling you for that.

Now for N_flag =1

Can I write like this?

proc sql;

    create table want as

    select max(ID) as ID, min(E_Flag) as E_Flag,

min(N_Flag) as N_Flag,

max(Center) as Center, min(tdate) as first_date format=date9.,

                        case when max(center)>2 then sum((center=2)*tdate)

                            else . end as second_date format=date9.,

                        max(tdate) as last_date format=date9.

    from have

    where E_Flag=1 and N_Flag=1

    group by ID

    order by id;

quit;

[Reeza]

No idea, don't know what your data is or what you're expecting. But there's no harm in trying

[Dipu]

No Problem. I am working on that. The best part is I understood the logic, now I can give a try.

Thank you so much Reeza.

Dipu