BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dipu
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

13 REPLIES 13
ballardw
Super User

Is TDATE a string value or a SAS date value?

What happens if E_flag has a value other than 1?

Dipu
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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

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
Calcite | Level 5

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
Super User

This assumes that Center and Tdate are both numeric.

Dipu
Calcite | Level 5

Right, both are numeric Reeza.

But I did not get the results.

:smileyplain:

Dipu
Calcite | Level 5

@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
Super User

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
Calcite | Level 5

You are awesome Reeza....Smiley Happy

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
Super User

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

Dipu
Calcite | Level 5

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.Smiley Happy

Dipu

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1530 views
  • 11 likes
  • 3 in conversation