Auditor changes

[jjadall1]

Hello SAS Support Communities,

I would like to investigate auditor changes as a part of my analysis.  I have attached two datasets.  When it comes to the rauditors dataset, there are four columns to compare:

1. _Auditor_During_Restated_Period

2. _Auditor_During_Restated_Period0

3. _Auditor_During_Restated_Period1

4. _Auditor_During_Restated_Period2

For most observations, there is only one auditor (column _Auditor_During_Restated_Period is the only field populated).  However, there are some observations where more than just the _Auditor_During_Restated_Period column is populated.  For those, some were auditor changes and some were not.

That's where we need the auditors dataset.  The two important columns in this dataset are prev_auditor_fkey and auditor_fkey.  With each observation, these represent the same auditor (for example, one auditor may have purchased another auditor).  

The key here is in the rauditors dataset, any auditors that are identified by a number in the prev_auditor_fkey column of the auditors dataset need to change to the auditor_fkey column of the auditors dataset.  This way, if there is a new number between columns (in the rauditors dataset), this will represent a real auditor change (not an auditor purchasing another auditor, merging, etc.).

To give you an example of what I'm looking for, look at observation 117 in the rauditors dataset.  The _Auditor_During_Restated_Period value is 7.  Now look at observation 1 in the auditors dataset.  Prev_auditor_fkey is 7 and the auditor_fkey is 11761.  Therefore, what I want is for the values in the rauditors dataset that have values in the prev_auditor_fkey column (in the auditors dataset) to CHANGE to the auditor_fkey values (in the auditors dataset).

Thus, I want observation 117 in the rauditors dataset to become the following:

1. _Auditor_During_Restated_Period (11761)

2. _Auditor_During_Restated_Period0 (11761)

3. _Auditor_During_Restated_Period1 (4)

4. _Auditor_During_Restated_Period2 (.)

After doing this, I would conclude that the only REAL auditor change occurred from _Auditor_During_Restated_Period0 to _Auditor_During_Restated_Period1.  Does that make sense?  That's what I'm looking for.

How would I go about addressing this between the two datasets?

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

[Patrick]

@jjadall1

It's unlikely that someone is going to solve the whole business problem for you. If you want answers from the community then you'll have to do the analysis and ask simplified technical questions for sub-problems you can't resolve.

[jjadall1 · Accepted solution]

Hello,

SAS Technical Support helped me out.  Here's the solution:

data dissert1.rauditors2(drop=found);                                                                                                                

  set dissert1.rauditors;                                                                                                                    

   found=0;                                                                                                                              

  do i=1 to n until (found);                                                                                                             

    set dissert1.auditors point=i nobs=n;                                                                                                  

     if prev_auditor_fkey = _Auditor_during_restated_period  then do;                                                                    

        found=1;                                                                                                                         

       _Auditor_during_restated_period=auditor_fkey ;    

    END;

  else _Auditor_during_restated_period=_Auditor_during_restated_period ;  

     if prev_auditor_fkey = _Auditor_during_restated_period0  then do;                                                                    

        found=1;                                                                                                                         

       _Auditor_during_restated_period0=auditor_fkey ;

   END;

  else _Auditor_during_restated_period0=_Auditor_during_restated_period0 ;   

     if prev_auditor_fkey = _Auditor_during_restated_period1  then do;                                                                    

        found=1;                                                                                                                         

       _Auditor_during_restated_period1=auditor_fkey ;   

   END;

    else _Auditor_during_restated_period1=_Auditor_during_restated_period1 ;  

     if prev_auditor_fkey = _Auditor_during_restated_period2  then do;                                                                    

        found=1;                                                                                                                         

       _Auditor_during_restated_period2=auditor_fkey ;    

   END;

    else _Auditor_during_restated_period2=_Auditor_during_restated_period2 ;  

     end;                                                                                                                                

run; 

God bless and best regards,

Jadallah

[jjadall1]

Hi all,

SAS Technical Support helped out!  Here's the code:

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;

God bless and best regards,

Jadallah