BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lixuan
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

7 REPLIES 7
art297
Opal | Level 21

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

 

ChrisNZ
Tourmaline | Level 20

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;

 

lixuan
Obsidian | Level 7

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

lixuan
Obsidian | Level 7

Also, all observations of A should be kept. Thanks 

ChrisNZ
Tourmaline | Level 20

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?

 

 

 

 

lixuan
Obsidian | Level 7

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

art297
Opal | Level 21

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

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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