DATA Step, Macro, Functions and more

Need help in correct use of retain and first. statement

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

Need help in correct use of retain and first. statement

Hi all, I have the data in following format 

medicaid_Id total_months start_month end_month
00001463A 12 22013 112015
00001773A 12 32013 12015
00004763A 1 32014 32014
00006014A 1 62014 32015
00007453A 12 12013 12016
00008433A 5 42013 112013
00009943A 7 72013 12014
00010933A 1 72013 72013
00010933A 1 32014 32014
00011183A 7 12013 12014
00011183A 1 62014 72015
00011184A 2 72013 82013
00043093A 4 72013 102013
00043093A 9 102013 122078

 

I need to count total_months for each medicaid_id. Fairly simple, but here is the catch. Some of those id's- which have multiple records- have the first time end_month as same as second time start_month or the second time end_month same as third time start_month. I need to subtract total months by 1 in those cases. Here is what I have coded and its not working. Help will be really appreciated.

 

data want;
set hAVE;
by medicaid_id start_month;
retain end_month =n;
if first.medicaid_id then total = total_months;
if not first.medicaid_id and start_month NE n -1 then total + total_months; else total +total_months - 1 ;

end;

run;


Accepted Solutions
Solution
‎10-10-2016 05:15 PM
Super User
Posts: 5,093

Re: Need help in correct use of retain and first. statement

Ah, that makes a whole lot more sense.  OK, here's one way:

 

data want;

set have;

by medicaid_id;

prior_end = lag(end_month);

if first.medicaid_id then total = total_months;

else do;

   total + total_months;

   if start_month = prior_end then total = total - 1;

end;

drop prior_end;

run;

View solution in original post


All Replies
Super User
Posts: 5,093

Re: Need help in correct use of retain and first. statement

First thing you better do is fix your data.  If you have a date like 12115, you can't tell if that's December 1 or January 21.

Frequent Contributor
Posts: 87

Re: Need help in correct use of retain and first. statement

Thanks. My bad, yes, i didnt notice that. Actually i used following method to get month year combine. On the other hand, it may not matter as its characters we are comparing, so it solves the purpose of identifying those cases where start month and end month of previous iteration is same.

 

data inal (drop = year month year_ month_);
set hh_2014_4;
month = month(start_dt);
year = year(start_dt);
start_month = compress(month||year);

month_ = month(end_dt);
year_ = year(end_dt);
end_month = compress(month_||year_);

run;

 

I'm sure there is better way to extract month and year than above?

Super User
Posts: 5,093

Re: Need help in correct use of retain and first. statement

If that code is producing the sample data you posted, there is definitely something wrong.  Why do some of your dates contain the day, when you are only putting together the month and the year?

 

I wouldn't feel confident in any solution until I know more about your original date variables.  When you run a PROC CONTENTS, what is in them?  I assume they are numeric, but do they have a format connected with them?  What do they look like when they are printed (unformatted vs. formatted)?

 

Once that is sorted out, it would probably be simpler to keep four variables instead of two:  start_month, start_year, end_month, and end_year.

Frequent Contributor
Posts: 87

Re: Need help in correct use of retain and first. statement

No sir, there are no days in the data i have or posted. First character is month (1 to 12) and after that its year (2013, 2014, 2015, 2016 or 2078)

 

These are the results after proc contents:

 

SAS Output

# Variable Type Len Format Informat
3 end_Dt Num 8 MMDDYY10. MMDDYY10.
6 end_month Char 24    
1 medicaid_Id Char 20   $20.
2 start_Dt Num 8 MMDDYY10. MMDDYY10.
5 start_month Char 24    
4 total_months Num 8    
Solution
‎10-10-2016 05:15 PM
Super User
Posts: 5,093

Re: Need help in correct use of retain and first. statement

Ah, that makes a whole lot more sense.  OK, here's one way:

 

data want;

set have;

by medicaid_id;

prior_end = lag(end_month);

if first.medicaid_id then total = total_months;

else do;

   total + total_months;

   if start_month = prior_end then total = total - 1;

end;

drop prior_end;

run;

Frequent Contributor
Posts: 87

Re: Need help in correct use of retain and first. statement

Wonderful! Yes, that did it..so it seems retain was not required here--im still new to lag function..

I have to further do this below to get distinct id's with total months..

proc sql;
create table want_ as select medicaid_id, max(total) as total_ from want group by medicaid_id; quit;

 

Hope that's correct.

Super User
Posts: 5,093

Re: Need help in correct use of retain and first. statement

In a way, RETAIN is required.  This statement is key:

 

total + total_months;

 

That form of statement (variable name + some numeric value) actually has a more complex set of meanings, one of which is to retain TOTAL.

 

Also note, if you only want a single observation per ID, the same DATA step can accomplish that.  Just before the RUN statement, add:

 

if last.medicaid_id;

 

Your SQL code looks like it works as well.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 384 views
  • 3 likes
  • 2 in conversation