the retain statement failed to retain as expected

Reply
Contributor
Posts: 49

the retain statement failed to retain as expected

I have a question on how to make the retain statement work for all new variable that I created.

 

In my dataset, I have multiple claims for each patient.

1. Each claim indicates a different inpatient event. Bene id is unique for each patient.

2. I sort the data by bene_id, service_from_day and service_thru_day and create a new variable 'start_dt_earAdmin', which is set as the index date by using a patient's ealiest service_from_day among his/her claims. I wish to assign the value of the first observation by bene_id to 'start_dt_earAdmin' with the sorted dataset.

3. With 'start_dt_earAdmin' as the index date, i would derive the end date as start_dt_earAdmin +364, which should be the same value for the same patient across his/her claims in the dataset.

 

My code:

 

data temp3.ipclms11_1; 
   retain start_dt_earAdmin end_dt_earAdmin day30_earAdmin day90_earAdmin ;
   set temp3.ipclms11_recode;
   by bene_id;
   format start_dt_earAdmin end_dt_earAdmin day30_earAdmin day90_earAdmin  YYMMDDN8.;
   if first.bene_id then do;
   start_dt_earAdmin=service_from_dt;
   end_dt_earAdmin=start_dt_earAdmin + 364;
   day30_earAdmin=start_dt_earAdmin +29 ;
   day90_earAdmin=start_dt_earAdmin +89;
   end;
run;

To my surprise, I have missing value on start_dt_earAdmin but no missing value on end_dt_earAdmin, day30_earAdmin or day90_earAdmin, which were derived from start_dt_earAdmin.

 

 

Table looks like:

 

bene_id  start_dt_earAdmin  end_dt_earAdmin  ...     service_from_dt  

1             01/01/12                            12/31/12                        01/01/12 

1                .                                      12/31/12                       05/01/13

2             01/01/13                            12/31/13                        01/01/13

2               .                                       12/31/13                        08/13/13

 

I got the problem fixed by adding code below:

 

%sortvar(data1=temp3.ipclms11_f,var1=bene_id service_from_dt  service_thru_dt);

data temp3.ipclms11_1 (drop=_start_dt_earAdmin); 
   set temp3.ipclms11_1 ;
   by bene_id;
   retain _start_dt_earAdmin;
   if first.bene_id then do;
   _start_dt_earAdmin=start_dt_earAdmin;
   end;
   else do;
     if missing(start_dt_earAdmin) then do;
     start_dt_earAdmin=_start_dt_earAdmin;
     end;
     else do;
      _start_dt_earAdmin=start_dt_earAdmin;
     end;
   end;
run;

 

Table now looks like:

bene_id  start_dt_earAdmin  end_dt_earAdmin  ...     service_from_dt  

1             01/01/12                            12/31/12                        01/01/12 

1             01/01/12                            12/31/12                       05/01/13

2             01/01/13                            12/31/13                        01/01/13

2             01/01/13                            12/31/13                        08/13/13

 

But I still couldn't figure out why I have missing value on start_dt_earAdmin in the first code. Is there any easier way of coding to achieve the expected dataset?

 

It would be great if you could share your thoughts with me. Thank you.

Super User
Posts: 11,134

Re: the retain statement failed to retain as expected

I think that you need to provide a log of the code submitted as I cannot duplicate your results with a dummy dataset.

 

There is also something else fishy with this:

bene_id  start_dt_earAdmin  end_dt_earAdmin  ...     service_from_dt  

1             01/01/12                            12/31/12      ​                  01/01/12 

 

Your format statement has all of these as YYMMDDN8. format if you add 364 to 01/01/12 you should not have the YEAR go from 01 to 12 for start_dt_earAdmin and end_dt_earAdmin. I expect teh the end_dt_earAdmin is coming from early records where it was assigne and retained but something is happening to the start_dt variable.

 

So I suspect something else is going on. Possibly getting different versions of the data crossed up?

Can you post some actual data from your temp3.ipclms11_recode data set in the form of a datastep with just the bene_id and service_from_dt variables?

Contributor
Posts: 49

Re: the retain statement failed to retain as expected

Thank you for your help.

Sorry, that was the data that I made up as example.

I'll attach the log and the real data here for your reference.

 

1067  data temp3.ipclms11_1;
1068     retain start_dt_earAdmin end_dt_earAdmin day30_earAdmin day90_earAdmin ;
1069     set temp3.ipclms11_recode;
1070     by bene_id;
1071     format start_dt_earAdmin end_dt_earAdmin day30_earAdmin day90_earAdmin  YYMMDDN8.;
1072     if first.bene_id then do;
1073     start_dt_earAdmin=service_from_dt;
1074     end_dt_earAdmin=start_dt_earAdmin + 364;
1075     day30_earAdmin=start_dt_earAdmin +29 ;
1076     day90_earAdmin=start_dt_earAdmin +89;
1077     end;
1078  run;

NOTE: There were 279467 observations read from the data set TEMP3.IPCLMS11_RECODE.
NOTE: The data set TEMP3.IPCLMS11_1 has 279467 observations and 20 variables.
NOTE: DATA statement used (Total process time):
      real time           8.79 seconds
      cpu time            0.43 seconds

Contributor
Posts: 49

Re: the retain statement failed to retain as expected

Here's the resulting data:

 

Obs

ID

start_dt_earAdmin

end_dt_earAdmin

day30_earAdmin

day90_earAdmin

service_from_dt

1

1

20110819

20120817

20110917

20111116

20110819

2

2

20110723

20120721

20110821

20111020

20110723

3

2

.

20120721

20110821

20111020

20110810

4

3

20110316

20120314

20110414

20110613

20110316

5

4

20111226

20121224

20120124

20120324

20111226

6

5

20110629

20120627

20110728

20110926

20110629

7

6

20110814

20120812

20110912

20111111

20110814

8

7

20111114

20121112

20111213

20120211

20111114

9

8

20110116

20120115

20110214

20110415

20110116

10

9

20110121

20120120

20110219

20110420

20110121

11

10

20110207

20120206

20110308

20110507

20110207

12

11

20110404

20120402

20110503

20110702

20110404

13

12

20110624

20120622

20110723

20110921

20110624

14

13

20110408

20120406

20110507

20110706

20110408

15

14

20110928

20120926

20111027

20111226

20110928

16

15

20110414

20120412

20110513

20110712

20110414

17

15

.

20120412

20110513

20110712

20111004

18

16

20111117

20121115

20111216

20120214

20111117

19

17

20110728

20120726

20110826

20111025

20110728

20

18

20110328

20120326

20110426

20110625

20110328

Super User
Posts: 5,369

Re: the retain statement failed to retain as expected

The first thing to look at is whether START_DT_EARADMIN is part of the input data set temp3.ipclms11_recode.  If so, that alone would be enough to explain the results you are seeing.

Ask a Question
Discussion stats
  • 4 replies
  • 342 views
  • 1 like
  • 3 in conversation