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. 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;
Is TDATE a string value or a SAS date value?
What happens if E_flag has a value other than 1?
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
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.
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
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;
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...
This assumes that Center and Tdate are both numeric.
Right, both are numeric Reeza.
But I did not get the results.
:smileyplain:
@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.
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;
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;
No idea, don't know what your data is or what you're expecting. But there's no harm in trying
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.