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.
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;
A few questions:
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;
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;
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?
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.
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.