Hello
Here is my dataset:
http://image.noelshack.com/fichiers/2015/24/1433856711-capture.png
I would like to do something like that:
data forecast2;
set fort_nb01;
if (d_report='01MAR15'd and (actual>upper or actual<lower)) then output fort_nb01(where d_report>='01JAN15'd);
run;
Of course it doesn't work but I tried thousands ways to do that (dubble if, if/where, select,...) and, when it works, I always can have only the row with '01MAR15'd but I would like (if this condition is true) rows from '01JAN15' to the end.
This problem seems very easy but I still have not found a solution.
Thanks for your help!
Each SET statement can have its own WHERE statement. This approach (untested) should work:
data want;
if _n_=1 then set have (where=(d_report='01MAR2015'd and (actual>upper or actual<lower)) );
set have;
where d_report>='01JAN2015'd;
run;
If the first SET statement retrieves 0 observations, the DATA step will be over. If it does find an observation meeting all 3 conditions, the second SET statement is free to read all observations (subsetting based on its own WHERE statement).
Note that you are treading on thin ice if you use two-digit years!
Good luck.
Its a good idea to post test data in the form of a datatep and required output. At a guess:
proc sql;
create table WANT as
select *
from (select * from HAVE where DATE >= "01JAN15"d)
where ID in (select distinct ID from HAVE where ACTUAL > UPPER or ACTUAL < LOWER);
quit;
Thanks but it is not really what I want. I was not clear, sorry.
I would like something like that : (I know it is totally impossible in SQl but it is to show the idea)
proc sql;
create table WANT as
if (date='01MAR15'd and ACTUAL > UPPER or ACTUAL < LOWER) then {
select *
from (select * from HAVE where date >= "01JAN15"d);
}
quit;
I check only the condition for March. If true, I create a new table with the rows from JAN to the end anyway the result of the condition for these months.
Thanks.
Still the same code, your just thinking about the problem in the other way round.
proc sql;
create table WANT as
select *
from (select * from HAVE where DATE >= "01JAN15"d)
/* So take all data from original dataset with a date after 01JAN15 *.
where ID in (select distinct ID from HAVE where (DATE="01MAR15"d and (ACTUAL > UPPER or ACTUAL < LOWER));
/* But restrict the main dataset to those ID's having on the Mar, actual outside the bounds */
quit;
Stupid question but what is ID in my case?
Thanks.
Some identifier in you data, e.g.
ID DATE ACTUAL UPPER LOWER
1 ....
1 ..
2 ..
Each SET statement can have its own WHERE statement. This approach (untested) should work:
data want;
if _n_=1 then set have (where=(d_report='01MAR2015'd and (actual>upper or actual<lower)) );
set have;
where d_report>='01JAN2015'd;
run;
If the first SET statement retrieves 0 observations, the DATA step will be over. If it does find an observation meeting all 3 conditions, the second SET statement is free to read all observations (subsetting based on its own WHERE statement).
Note that you are treading on thin ice if you use two-digit years!
Good luck.
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.