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