Find last date value in a field and return value to a new field

[Haydn]

Hi,

I have the following code, which merges monthly data sets. Each dataset will have a field called 'report_date' and this will have the month and year. Example the monthlytblejul2015 dataset will have JUL2015, the monthlytbledec2015 will have DEC2015 in the 'report_date' field. What I am trying to achieve is create a new field, called "Report_Month" in the merged dataset and  every row return the last value in the "report_date' field, in the case that would be DEC2015. Next month when I merge January 2016 data to the dataset, this value would then be JAN2016. I have tried Max, but this duplicates the 'report_date' column.

Any help is appreciated

data vaload.ind_monthly_report;
merge 
reports.monthlytblejul2015          
reports.monthlytbleaug2015 
reports.monthlytblesep2015
reports.monthlytbleoct2015  
reports.monthlytblenov2015
reports.monthlytbledec2015
;
 
by  report_date;     
 Report_Month= max(report_date) ; 
format Report_Month monyy7.;

run;

[Jagadishkatam]

we could merge the datasets without the by statement like below and rename the dates in every dataset, by this way all the datasets will align side by side with different dates and then you could create a new variable with the max date.

By using the by statement you are trying to merge the datasets on dates, since they do not have the common dates in all datasets, merge will not occur.

This is an untested code, please try and let me know if it helps.

data vaload.ind_monthly_report;
merge 
reports.monthlytblejul2015(rename=(report_date=report_date1))          
reports.monthlytbleaug2015 (rename=(report_date=report_date2))
reports.monthlytblesep2015(rename=(report_date=report_date3))
reports.monthlytbleoct2015  (rename=(report_date=report_date4))
reports.monthlytblenov2015(rename=(report_date=report_date5))
reports.monthlytbledec2015(rename=(report_date=report_date6))
;
     
Report_Month= max(of report_date1-report_date6) ; 
format Report_Month monyy7.;
run;

[Haydn]

Thanks Jag,

That does give me a me field with the latest date. Is there any way I can keep the original 'report_date' field with it's values, as I use this in SAS VA to trend this report over time?

[Jagadishkatam]

if i understand you correctly, you want to have the report_date variable merged from different datasets same in the final output. if this is the case, then I am not sure if there is a way to have the same variable name for different columns in same dataset, if you want to disaply all of the columns. However if you want to display the most recent dataset with same column name then it is possible.

Please try

data vaload.ind_monthly_report;
merge 
reports.monthlytblejul2015(rename=(report_date=report_date1))          
reports.monthlytbleaug2015 (rename=(report_date=report_date2))
reports.monthlytblesep2015(rename=(report_date=report_date3))
reports.monthlytbleoct2015  (rename=(report_date=report_date4))
reports.monthlytblenov2015(rename=(report_date=report_date5))
reports.monthlytbledec2015
;
Report_Month= max(report_date1, report_date2, report_date3, report_date4, report_date5, report_date6, report_date) ; 
format Report_Month monyy7.;
run;

[Tom · Accepted solution]

Examples of actual numbers would help.  I suspect that you want to use a SET statement to combine the tables vertically instead of a MERGE statement which would try to merge the data horizontally.

data vaload.ind_monthly_report;
  set 
    reports.monthlytblejul2015          
    reports.monthlytbleaug2015 
    reports.monthlytblesep2015
    reports.monthlytbleoct2015  
    reports.monthlytblenov2015
    reports.monthlytbledec2015
  ;
  by  descending report_date ;
  if _n_=1 then Report_Month= report_date ;
  retain Report_Month;
  format Report_Month monyy7.;
run;  

[Haydn]

Thanks Tom and Jag for you assistance, very much appreciated.

Tom your solution was exactly what I was after.

Cheers

Haydn