DATA Step, Macro, Functions and more

Copying Field Values Based on Another Field

Reply
Regular Contributor
Posts: 152

Copying Field Values Based on Another Field

[ Edited ]

 

I am using SAS 9.3.

 

I have some patient data where one line of code is one patient visit.  In the procedure data, the field of procedure date and doctor is only entered on occurrence 1 but not for other occurrences included in the same date.  The code attached fixes that except for the out of hospital field (OOH1-OOH4).  The OOH field is either Y or blank and with the example attached, record 4 should have a Y for OOH2 and OOH3 but not for OOH4.  So I'm trying to figure out how to copy down within the same episode but if the date changes to signify a new procedural episode and the first occurrence of that new procedural episode isn't "Y" then no others in that episode will be. 

 

Thanks for any and all assistance.

 

 

 

 

 

 

Super User
Posts: 5,256

Re: Copying Field Values Based on Another Field

I think the reason for inconsistent data which leads leads to more or less complicated transformations, is that the data is not normalized from the beginning.
Storing the data in a better structure will let you easily and in a consistent way query with simple joins.
Data never sleeps
Regular Contributor
Posts: 152

Re: Copying Field Values Based on Another Field

Hi

 

Thanks for responding.  The data actually isn't "inconsistent", this is the format that the data is always saved in via other software so I can't do anything about that.  Is there not a way to get at the information I require?  Thanks.

Trusted Advisor
Posts: 1,115

Re: Copying Field Values Based on Another Field

[ Edited ]

Hello @shellp55,

 

Isn't it possible to handle OOH same way as PxDoc and PxStDate? That is, define a fourth array

array OOH{4};

and insert the following line as a third line into the DO-END block

if PxStDate{i} = PxStDate{i-1} then OOH{i} = coalescec(OOH{i},OOH{i-1});

As the missing dates have been imputed already at this point, the additional IF condition ensures that the previous OOH value is copied only if the date (i.e. episode) is still the same.

 

Edit: I had difficulties copying your sample code because I don't have Word on my SAS workstation. Please use the "Insert SAS Code" feature ("running man" icon) in the future to post SAS code.

Regular Contributor
Posts: 152

Re: Copying Field Values Based on Another Field

Thanks for your response.  I found that using the same code as what is there puts "Y" in all the OOH.

 

Note also that I tried to use the code insert but it wouldn't accept all the code, even when I split it in two so that is why I had to use as attachment.  However, in future I will save to WordPad since most users will have that. 

Trusted Advisor
Posts: 1,115

Re: Copying Field Values Based on Another Field

[ Edited ]

Strange. It worked with my SAS 9.4. However, I had to introduce variable OOH4 into TEST_GRP, because it seemed to be missing in your code.

data test_grp ;
infile datalines truncover;
input @1 regno $3.
      @4 PxStDate1 yymmdd8.
      @12 PxDoc1 $5. 
      @17 Px1 $7.  
      @24 OOH1 $1.  
      @25 PxStDate2 yymmdd8. 
      @33 PxDoc2 $5.  
      @38 Px2 $7.  
      @45 OOH2 $1. 
      @46 PxStDate3 yymmdd8. 
      @54 PxDoc3 $5.
      @59 Px3 $7.  
      @66 OOH3 $1.  
      @67 PxStDate4 yymmdd8.
      @75 PxDoc4 $5. 
      @80 Px4 $7.
      @87 OOH4 $1.;
format PxStDate1-PxStDate4 yymmdd10.;
cards;
00120120329Smith1RB89LA         Jones1RB87LA              2OT71LA 
00220120417Jones1RM89LA              1RD89LA 20120420Green3OT20WE
00320120420Brown1VA53LA 20120425Jones1VC55LA 20120430Green3OT20WE
00420120513Green1RM89LAY             1RD89LA              2OT71LA 20120525Jones3OT20WE
00520120602Smith1VA53LA              1VC55LA              2OT20WE 20120623Jones3OT20WE
00620120329Brown1RB89LA              1RB87LA              2OT71LA
007
run;

data want;
set test_grp; 
array PxDoc{*} PxDoc1-PxDoc4;
array PxStDate{*} PxStDate1-PxStDate4;
array Px{*} Px1-Px4;
array OOH{4};
do i = 2 to 4;
  if not missing(Px{i}) then do;
    PxDoc{i} = coalescec(PxDoc{i},PxDoc{i-1});           
    PxStDate{i} = coalesce(PxStDate{i},PxStDate{i-1});           
    if PxStDate{i} = PxStDate{i-1} then OOH{i} = coalescec(OOH{i},OOH{i-1});
  end;      
end; 
drop i; 
run;  

proc print noobs;
run;

(Please note: The above code was inserted using the "Insert SAS Code" feature.)

 

Edit: And the output below was inserted using the "Insert Code" feature ({i} button).

                    Px                                Px                                Px                                Px
regno   PxStDate1  Doc1     Px1    OOH1   PxStDate2  Doc2     Px2    OOH2   PxStDate3  Doc3     Px3    OOH3   PxStDate4  Doc4     Px4    OOH4

 001   2012-03-29  Smith  1RB89LA        2012-03-29  Jones  1RB87LA        2012-03-29  Jones  2OT71LA                 .
 002   2012-04-17  Jones  1RM89LA        2012-04-17  Jones  1RD89LA        2012-04-20  Green  3OT20WE                 .
 003   2012-04-20  Brown  1VA53LA        2012-04-25  Jones  1VC55LA        2012-04-30  Green  3OT20WE                 .
 004   2012-05-13  Green  1RM89LA   Y    2012-05-13  Green  1RD89LA   Y    2012-05-13  Green  2OT71LA   Y    2012-05-25  Jones  3OT20WE
 005   2012-06-02  Smith  1VA53LA        2012-06-02  Smith  1VC55LA        2012-06-02  Smith  2OT20WE        2012-06-23  Jones  3OT20WE
 006   2012-03-29  Brown  1RB89LA        2012-03-29  Brown  1RB87LA        2012-03-29  Brown  2OT71LA                 .
 007            .                                 .                                 .                                 .
Regular Contributor
Posts: 152

Re: Copying Field Values Based on Another Field

[ Edited ]

Thanks...but note that not all of the program was copied via insert code.  It doesn't show all of the "want" data set program.

 

Also, will what you've suggested work if the data does have entries?  For instance, if OOH1 and OOH2 both have "Y" will the code you provide still allow OOH3 to have "Y" added? 

 

Thanks.

Trusted Advisor
Posts: 1,115

Re: Copying Field Values Based on Another Field


shellp55 wrote:

(...)

Also, will what you've suggested work if the data does have entries?  For instance, if OOH1 and OOH2 both have "Y" will the code you provide still allow OOH3 to have "Y" added?


 

If OOH3 is blank and OOH2='Y' (and both IF conditions are met), OOH3 will be set to 'Y', because this is the result of coalescec(OOH{3},OOH{2}). But that's the same technique you already applied to PxDoc and PxStDate.

 

Regular Contributor
Posts: 152

Re: Copying Field Values Based on Another Field

Sorry but I've just come up with a new wrinkle on this.  What if the procedure date is copied down?  The criteria for whether to copy the "Y" is based on whether the initial occurrence has a Y and if the date is the same.  So if the 3rd procedure has a new date and the 3rd procedure doesn't have "Y" then procedure 4 won't have Y either.

 

Thanks.

Trusted Advisor
Posts: 1,115

Re: Copying Field Values Based on Another Field

Hello @shellp55,

 

No problem. We'll work on this task as long as it takes to obtain a satisfactory solution.

 

So, did you observe or can you construct a (realistic) data record for which the current algorithm produces an incorrect result?

 

If the procedure date is copied down, this will occur before the corresponding OOH value is (potentially) imputed.

 

Currently, there are three conditions, all of which must be met, in order to have a missing value of OOHi (i=2, 3, or 4) imputed by 'Y':

  1. Pxi is not missing
  2. PxStDatei=PxStDate(i-1) (after imputing PxStDatei if it was missing, as mentioned above)
  3. OOH(i-1)='Y' (this condition is implicitly contained in the COALESCEC function call)

The third condition above is not equivalent to what you wrote in todays post, though: An example case where they would produce different results is that

  1. OOH1 (the value pertaining to the "initial occurrence") has a missing value
  2. OOH2='Y'
  3. OOH3 is missing
  4. Px3 is not missing
  5. PxStDate3=PxStDate2 (after imputation, if any)

In this case, the current algorithm would set OOH3 to 'Y' (because the three conditions listed further above are met), although OOH1 is missing.

 

So, the question is: Is this a scenario that can realistically occur in your data, OOH having its first 'Y' only in "column" 2 (i.e. in OOH2) and should this prevent the imputation of the missing value in OOH3?

 

If so, we could add OOH1='Y' as another IF condition to the line of code where OOH{i} is imputed.

Ask a Question
Discussion stats
  • 9 replies
  • 427 views
  • 0 likes
  • 3 in conversation