i am expecting if any yes in a row display yes, if 'No' and blank in row display No and if all blank display No.
data val.compare_results(drop= i);
set val.compare_results;
length Impacted_Patch $4;
array val (*) &varlist.;
do i=1 to dim(Val);
if Val{i} in ('Yes') then Impacted_Patch='Yes';
else if Val{i} = (' ') then Impacted_Patch=' ';
else if Val{i} in ('No') and Val{i} in (' ') then Impacted_Patch='No';
end;
keep ID Val_required_Nov Val_required_Dec Val_required_Jan Val_required_Feb Val_required_March Val_required_April Impacted_Patch;
run;
my results:
ID | Val_required_Nov | Val_required_Dec | Val_required_Jan | Val_required_Feb | Val_required_March | Val_required_April | Impacted_Patch |
5Z4PT7 | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
5Z4PTD | Yes | No | Yes | Yes | Yes | Yes | |
5Z4PTE | No | Yes | Yes | Yes | Yes | ||
5Z4PTP | No | No | No | No | |||
5Z4PTR | Yes | Yes | Yes | Yes | Yes | No | Yes |
5Z4PTX | Yes | Yes | Yes | Yes | Yes | Yes | |
5Z4PU2 | Yes | Yes | Yes | Yes | Deferred | Yes | |
5Z4PU3 | No | No | Yes | Yes | Yes | Yes | |
5Z4PU5 | Yes | Yes | No | Yes | No | Yes | Yes |
5Z4PU8 | Deferred | Yes | No | Yes | Yes | Yes | Yes |
5Z4PUA | |||||||
5Z4PUM | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
5Z4PUZ | No | Yes | No | No | Yes | ||
5Z4PV3 | |||||||
5Z4PVC | |||||||
5Z4PVP | No | Yes | Yes | Yes | Yes | ||
5Z4PVX | Yes | Yes | No | Yes | No | ||
5Z4PVY | Yes | Yes | No | Yes | No | Yes | Yes |
5Z4PVZ | Yes | Yes |
expected results:
ID | Val_required_Nov | Val_required_Dec | Val_required_Jan | Val_required_Feb | Val_required_March | Val_required_April | Impacted_Patch | expected |
5Z4PT7 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | yes |
5Z4PTD | Yes | No | Yes | Yes | Yes | Yes | yes | |
5Z4PTE | No | Yes | Yes | Yes | Yes | yes | ||
5Z4PTP | No | No | No | No | No | |||
5Z4PTR | Yes | Yes | Yes | Yes | Yes | No | Yes | yes |
5Z4PTX | Yes | Yes | Yes | Yes | Yes | Yes | yes | |
5Z4PU2 | Yes | Yes | Yes | Yes | Deferred | Yes | yes | |
5Z4PU3 | No | No | Yes | Yes | Yes | Yes | yes | |
5Z4PU5 | Yes | Yes | No | Yes | No | Yes | Yes | yes |
5Z4PU8 | Deferred | Yes | No | Yes | Yes | Yes | Yes | yes |
5Z4PUA | ||||||||
5Z4PUM | Yes | Yes | Yes | Yes | Yes | Yes | Yes | yes |
5Z4PUZ | No | Yes | No | No | Yes | yes | ||
5Z4PV3 | ||||||||
5Z4PVC | ||||||||
5Z4PVP | No | Yes | Yes | Yes | Yes | yes | ||
5Z4PVX | Yes | Yes | No | Yes | No | yes | ||
5Z4PVY | Yes | Yes | No | Yes | No | Yes | Yes | yes |
5Z4PVZ | Yes | Yes | yes | |||||
5Z4PW8 | No | No | No | No | No | |||
5Z4PWB | No | No | Deferred | Yes | No | yes |
The main problem you are running into is that you are changing the value of IMPACTED_PATCH each time you inspect a new variable in the array. While there are many ways to change that, here's one that involves minimal change to your current program.
Your current code:
do i=1 to dim(Val);
if Val{i} in ('Yes') then Impacted_Patch='Yes';
else if Val{i} = (' ') then Impacted_Patch=' ';
else if Val{i} in ('No') and Val{i} in (' ') then Impacted_Patch='No';
end;
The replacement code:
do i=1 to dim(Val) until (Impacted_Patch='Yes');
if Val{i} in ('Yes') then Impacted_Patch='Yes';
else if Val{i} in ('No') then Impacted_Patch='No';
end;
It's not clear if you want anything to happen when a variable is "Deferred" so the current logic ignores that.
The main problem you are running into is that you are changing the value of IMPACTED_PATCH each time you inspect a new variable in the array. While there are many ways to change that, here's one that involves minimal change to your current program.
Your current code:
do i=1 to dim(Val);
if Val{i} in ('Yes') then Impacted_Patch='Yes';
else if Val{i} = (' ') then Impacted_Patch=' ';
else if Val{i} in ('No') and Val{i} in (' ') then Impacted_Patch='No';
end;
The replacement code:
do i=1 to dim(Val) until (Impacted_Patch='Yes');
if Val{i} in ('Yes') then Impacted_Patch='Yes';
else if Val{i} in ('No') then Impacted_Patch='No';
end;
It's not clear if you want anything to happen when a variable is "Deferred" so the current logic ignores that.
Since you are looking at "any yes" and "all no"
I might be tempted to do it this way: (Untested as data step to create your example data not provided)
temp= catx(',', of val[*]); if findw(temp,'Yes')>0 then Impacted_patch='Yes'; else if findw(temp,'No')>0 then Impacted_patch='No'; drop temp;
Note that the above code will set NO for records involving combinations of Deferred and only No or missing and set missing if the only value is Deferred.
You could use the function call that creates temp instead of the variable temp but this way if you can leave temp in while debugging
This is also a good example of why coding character Yes No values is poor. If you had them as numeric yes=1 and no=yes
Impacted_patch = ifc(sum(of val[*]),1,0,.); /* of if you insist Impacted_patch must be character*/ Impacted_patch = ifc(sum(of val[*]),'Yes','No','');
Of course neither of these address "deferred" as you provided no rule for what to do if only that value occurs, or occurs in combination with all other NO or blank.
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 25. Read more here about why you should contribute and what is in it for you!
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.