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.
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.
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.
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.
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 . . . .
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.
@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.
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.
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':
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
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.
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.
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.