hello
my data like this
data new;
input company$ forecast yymm;
cards;
A 1 198503
A 1 195503
A 0 198503
A 0 198504
A 1 198504
B 1 198503
B 1 198503
B 1 198504
C 0 198504
C 1 198504
;
run;
There is no missing data. I would like to every observation in every date has the variable '0,1'. If can not, the observation should be deleted. For example, A in 198503 and in 198504 which both have variable ‘0&1’ should be kept, while B in 198504 which only has variable '0' should be deleted. So the result should only remain observation A.
Thanks a lot.
Another way:
proc sql;
select src.*
from HAVE src
,(select COMPANY, YYMM, count(unique(FORECAST)) as C
from HAVE
group by 1,2
having C=2) vet
where src.COMPANY=vet.COMPANY
& src.YYMM =vet.YYMM;
quit;
Here is one way to do it:
data want; do until (last.company); set new; by company; if first.company then do; _have0=0; _have1=0; end; if forecast=1 then have1=1; else if forecast=0 then have0=1; end; do until (last.company); set new; by company; if have0 and have1 then output; end; run;
HTH,
Art, CEO, AnalystFinder.com
Another way:
proc sql;
select src.*
from HAVE src
,(select COMPANY, YYMM, count(unique(FORECAST)) as C
from HAVE
group by 1,2
having C=2) vet
where src.COMPANY=vet.COMPANY
& src.YYMM =vet.YYMM;
quit;
Hi, thank you very much , but C should also be deleted, because C don't have '0&1' in 198503. Thanks again.
Also, all observations of A should be kept. Thanks
In this case, you havent explained you logic properly.
Are you saying that company A should be kept because it includes, somewhere in its records, all 4 dates and all 2 forecasts? regardless of any crossing between date and forecast?
If so, how do we get the list of all dates to check against in order for a company to be kept?
I really appreciate all of you. My problem has been resolve. Thank you
c had 2 records, one with a 1, and the 2nd with a 0. That is why the code kept both a and c.
Art, CEO, AnalystFinder.com
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.