Please Help Computing the Values using Data step or proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Please Help Computing the Values using Data step or proc sql

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


Accepted Solutions
Solution
‎02-12-2014 05:23 PM
Super User
Posts: 17,745

Re: Please Help Computing the Values using Data step or proc sql

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


All Replies
Super User
Posts: 10,464

Re: Please Help Computing the Values using Data step or proc sql

Is TDATE a string value or a SAS date value?

What happens if E_flag has a value other than 1?

Contributor
Posts: 43

Re: Please Help Computing the Values using Data step or proc sql

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

Super User
Posts: 17,745

Re: Please Help Computing the Values using Data step or proc sql

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. 

Contributor
Posts: 43

Re: Please Help Computing the Values using Data step or proc sql

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

Super User
Posts: 17,745

Re: Please Help Computing the Values using Data step or proc sql

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;

Contributor
Posts: 43

Re: Please Help Computing the Values using Data step or proc sql

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

Super User
Posts: 17,745

Re: Please Help Computing the Values using Data step or proc sql

This assumes that Center and Tdate are both numeric.

Contributor
Posts: 43

Re: Please Help Computing the Values using Data step or proc sql

Right, both are numeric Reeza.

But I did not get the results.

:smileyplain:

Contributor
Posts: 43

Re: Please Help Computing the Values using Data step or proc sql

@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.

Solution
‎02-12-2014 05:23 PM
Super User
Posts: 17,745

Re: Please Help Computing the Values using Data step or proc sql

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;

Contributor
Posts: 43

Re: Please Help Computing the Values using Data step or proc sql

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;

Super User
Posts: 17,745

Re: Please Help Computing the Values using Data step or proc sql

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

Contributor
Posts: 43

Re: Please Help Computing the Values using Data step or proc sql

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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