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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.