topic Please Help Computing the Values using Data step or proc sql in SAS Programming

Please Help Computing the Values using Data step or proc sql

Dipu — Wed, 12 Feb 2014 17:09:58 GMT

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

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

ballardw — Wed, 12 Feb 2014 20:22:07 GMT

Is TDATE a string value or a SAS date value?

What happens if E_flag has a value other than 1?

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

Dipu — Wed, 12 Feb 2014 20:35:27 GMT

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

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

Reeza — Wed, 12 Feb 2014 20:54:21 GMT

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.

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

Dipu — Wed, 12 Feb 2014 20:58:46 GMT

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

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

Reeza — Wed, 12 Feb 2014 21:28:46 GMT

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;

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

Dipu — Wed, 12 Feb 2014 21:46:13 GMT

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

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

Reeza — Wed, 12 Feb 2014 21:51:28 GMT

This assumes that Center and Tdate are both numeric.

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

Dipu — Wed, 12 Feb 2014 22:01:56 GMT

Right, both are numeric Reeza.

But I did not get the results.

:smileyplain:

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

Dipu — Wed, 12 Feb 2014 22:06:48 GMT

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

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

Reeza — Wed, 12 Feb 2014 22:23:49 GMT

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;

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

Dipu — Wed, 12 Feb 2014 22:51:48 GMT

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;

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

Reeza — Wed, 12 Feb 2014 23:00:44 GMT

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

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

Dipu — Wed, 12 Feb 2014 23:03:23 GMT

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