BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jjadall1
Quartz | Level 8

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 
1 ACCEPTED SOLUTION

Accepted Solutions
jjadall1
Quartz | Level 8

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

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

@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
Quartz | Level 8

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 884 views
  • 4 likes
  • 2 in conversation