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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.