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;
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;
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,
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?
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;
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;
Thanks Tom and Jag for you assistance, very much appreciated.
Tom your solution was exactly what I was after.
Cheers
Haydn
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: