topic Re: Pull all data for ID when another column meets 2 criteria in New SAS User

Pull all data for ID when another column meets 2 criteria

t_ng — Wed, 18 Jan 2023 06:04:28 GMT

Hello,

I have the following data and I'm trying to pull all the data from an ID when another column with the following combination:

- Yes and Yes (different)

- Yes and No

- Yes and Maybe

- Yes, No, and Maybe

Team	ID	Department	Result
1	111111	Math	Yes
1	111111	Science	Yes
2	111222	Science	Yes
2	111222	English	Maybe
2	111333	Math	Yes
2	111333	Art	No
3	111444	Art	No
3	111555	Science	Yes
3	111555	English	Maybe
3	111555	Art	No
4	111666	History	Maybe
4	111666	Art	No

What I'm looking for is as follows.

Team	ID	Department	Result
1	111111	Math	Yes
1	111111	Science	Yes
2	111222	Science	Yes
2	111222	English	Maybe
2	111333	Math	Yes
2	111333	Art	No
3	111555	Science	Yes
3	111555	English	Maybe
3	111555	Art	No

Any help would be greatly appreciated.

Re: Pull all data for ID when another column meets 2 criteria

PeterClemmensen — Wed, 18 Jan 2023 07:49:10 GMT

A few questions:

By 'column', you mean ID group, correct? You want an ID to contain either of the four listed combinations. Otherwise, the group as a whole should be deleted, right?
Does the Team matter? Can an ID belong to more than one team?

Re: Pull all data for ID when another column meets 2 criteria

t_ng — Wed, 18 Jan 2023 12:47:29 GMT

Yes, correct ID group that contains either of the four listed combination. The whole ID group would be deleted if it does not contain either of the combination. Table above shows ID group 111444 and 111666 has be removed.
The Team does matter but would need to be in the final output. Yes, ID group could belong to more than one Team.

Re: Pull all data for ID when another column meets 2 criteria

PaigeMiller — Wed, 18 Jan 2023 16:25:54 GMT

Hello @t_ng and welcome to the SAS Communities.

I realize you are new here, so some advice ... we cannot write code that works on data in a screen capture. Data should be presented as SAS data step code, and not in any other format. See the example below that creates data set named HAVE. Please follow this example in the future.

I believe this will get the desired result:

data have; 
input Team ID Department $ Result $;
cards;
1 111111 Math Yes
1 111111 Science Yes
4 111666 History Maybe
4 111666 Art No
;
proc transpose data=have out=have_t prefix=result;
    by id team;
    var result;
run;
data have_t1;
    set have_t;
    array r result:;
    number_yes=0;
    number_no=0;
    number_maybe=0;
    do i=1 to dim(r);
        number_yes=number_yes+(upcase(r(i))='YES');
        number_no=number_no+(upcase(r(i))='NO');
        number_maybe=number_maybe+(upcase(r(i))='MAYBE');
    end;
    drop i;
run;
data want;
    merge have have_t1(keep=number: id team);
    by id team;
    if number_yes>1 or (number_yes=1 and number_no>=1) or (number_yes=1 and number_maybe>=1);
    drop number:;
run;

Re: Pull all data for ID when another column meets 2 criteria

t_ng — Thu, 19 Jan 2023 08:06:20 GMT

Hello @PaigeMiller , thank you. I would remember that for the future request.

This data is a made-up data, so some information was missed. I had omitted a column and 2 columns are a not numbers. Could you please take another look at the following SAS code.
I have 2 errors which I have noted in /* */ in the SAS code.
t1.Team has a mix of numbers and letters with numbers (example: TA01) and everything else are strings. That's how it was structured in the original tables.
The want would contain Team, EventID, ID, Department, and Result.

PROC SQL;
   CREATE TABLE WORK.Have AS 
   SELECT DISTINCT t1.Team,
          t2.EventID,
		  t2.ID,
          t3.Department,
          t4.Result
      FROM AAA.Table1 t1, AAA.Table2 t2, AAA.Table3 t3, WORK.Table4 t4, 
      WHERE (t1.Key1 = t2.Key1 AND t1.Key1 = t3.Key1 AND t3.Key2 = t4.Key2 AND (t1.DT >= '1Oct2022:0:0:0'dt AND t1.Active = 'A');
QUIT;

proc transpose data=WORK.Have out=WORK.Have2 prefix=Result;
    by ID Team;
    var Result;
run;

/* ERROR: Data set WORK.Have is not sorted in ascending sequence. The current BY group has Team = TA01 and the next BY group has Team = 
       2900. */

data WORK.Have3;
    set WORK.Have2;
    array r Result:;
    number_yes=0;
    number_no=0;
    number_maybe=0;
    do i=1 to dim(r);
        number_yes=number_yes+(upcase(r(i))='YES');
        number_no=number_no+(upcase(r(i))='NO');
        number_maybe=number_maybe+(upcase(r(i))='MAYBE');
    end;
    drop i;
run;

data want;
    merge WORK.Have WORK.Have3(keep=number: ID Team);

/* ERROR: The variable Team in the DROP, KEEP, or RENAME list has never been referenced. */

    by ID Team;
    if number_yes>1 or (number_yes=1 and number_no=1) or (number_yes=1 and number_maybe=1);
    drop number:;
run;

Re: Pull all data for ID when another column meets 2 criteria

PaigeMiller — Thu, 19 Jan 2023 11:15:41 GMT

Please make up some data that is consistent with the problem, and show that to us in the requested format.

Let's start with the first error message

/* ERROR: Data set WORK.Have is not sorted in ascending sequence. The current BY group has Team = TA01 and the next BY group has Team = 
       2900. */

It says the data is not sorted in ascending sequence. What is the problem? What do you think the solution is?

Re: Pull all data for ID when another column meets 2 criteria

t_ng — Tue, 24 Jan 2023 07:30:50 GMT

Sorry for the late reply. I took this back to my team and was trying to figure out what we were expecting. Then we realized there are additional data to include. I'll close this out for now until we figure out what we need exactly for this report. I'll accept the first answer as the solution since that SAS coding did generate the right data for the made-up data. Thank you.