Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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

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;

Accepted Solutions
Solution
‎02-02-2016 10:05 PM
Super User
Super User
Posts: 6,367

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

[ Edited ]

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;  

 

View solution in original post


All Replies
Super User
Posts: 1,117

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

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;
Thanks,
Jag
Contributor
Posts: 37

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

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?

Super User
Posts: 1,117

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

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;
Thanks,
Jag
Solution
‎02-02-2016 10:05 PM
Super User
Super User
Posts: 6,367

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

[ Edited ]

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;  

 

Contributor
Posts: 37

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

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

 

Tom your solution was exactly what I was after.

 

Cheers

Haydn

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 350 views
  • 3 likes
  • 3 in conversation