DATA Step, Macro, Functions and more

how to delete row observations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 97
Accepted Solution

how to delete row observations

[ Edited ]

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.


Accepted Solutions
Solution
‎03-10-2017 11:32 AM
PROC Star
Posts: 1,558

Re: how to delete row observations

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;

 

View solution in original post


All Replies
PROC Star
Posts: 7,356

Re: how to delete row observations

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

 

Solution
‎03-10-2017 11:32 AM
PROC Star
Posts: 1,558

Re: how to delete row observations

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;

 

Frequent Contributor
Posts: 97

Re: how to delete row observations

Hi, thank you very much , but C should also be deleted, because C don't have '0&1' in 198503. Thanks again.

Frequent Contributor
Posts: 97

Re: how to delete row observations

Also, all observations of A should be kept. Thanks 

PROC Star
Posts: 1,558

Re: how to delete row observations

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?

 

 

 

 

Frequent Contributor
Posts: 97

Re: how to delete row observations

I really appreciate all of you. My problem has been resolve. Thank you 

PROC Star
Posts: 7,356

Re: how to delete row observations

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 231 views
  • 0 likes
  • 3 in conversation