Data Management of Auditors

[jjadall1]

Hello SAS Support Communities,

Here's what I'm looking for now with the attached dataset (new 10):

The variables of interest are:

1. Auditor_Key 

2. _Auditor_During_Restated_Period

3. _Auditor_During_Restated_Period0

4. _Auditor_During_Restated_Period1

5. _Auditor_During_Restated_Period2

If there is only 1 auditor in the 4 columns (_Auditor_During_Restated_Period, _Auditor_During_Restated_Period0, _Auditor_During_Restated_Period1, and _Auditor_During_Restated_Period2), then a new variable (Auditor) should be created with that value (the 1 auditor).

If there is more than 1 auditor in the 4 columns (_Auditor_During_Restated_Period, _Auditor_During_Restated_Period0, _Auditor_During_Restated_Period1, and _Auditor_During_Restated_Period2), then the new Auditor value should have the value in the Auditor_Key column.

Let me give you some examples:

1. Observation 1 only has a value (4) in the _Auditor_During_Restated_Period.  The other 3 columns (_Auditor_During_Restated_Period0, _Auditor_During_Restated_Period1, and _Auditor_During_Restated_Period2) are blank.  Therefore, the new Auditor variable should have a value of 4.

2. Observation 8 has a value (26516) for both _Auditor_During_Restated_Period and _Auditor_During_Restated_Period0.  The other 2 columns (_Auditor_During_Restated_Period1 and _Auditor_During_Restated_Period2) are blank.  Since the value (26516) is the SAME, then the new Auditor variable should have the same value (26516).

3. Observation 424 has a value of 16168 for _Auditor_During_Restated_Period and 2830 for _Auditor_During_Restated_Period0.  Since these values are DIFFERENT, then the new Auditor variable should have the Auditor_Key value (2830).

Please let me know if you have any questions.

God bless, best regards, and thanks so much for your help,

Jadallah

[jjadall1 · Accepted solution]

data dissert1.new11;
set dissert1.new10;
array temp (*) _Auditor_During_Restated_Period _Auditor_During_Restated_Period0 _Auditor_During_Restated_Period1 _Auditor_During_Restated_Period2;
totalnum=n(_Auditor_During_Restated_Period, _Auditor_During_Restated_Period0, _Auditor_During_Restated_Period1, _Auditor_During_Restated_Period2);
count=0;
do i= 1 to dim(temp)-1;
if i=1 then count=1;
if totalnum=1 and temp(i) ne . then do;
newaudit=temp(i);
leave;
end;
if temp(i)=temp(i+1) then count+1;
if count=totalnum then do;
newaudit=temp(i);
leave;
end;
end;
run;