I have two tabes need to combine, for monthly payment , see atatch file format as refernce.
Table1 columns:
LOB , Code, Code_Desc, Original_Payment, Current_Payment, 201701
Table2 columns:
LOB , Code, Code_Desc, Original_Payment, Current_Payment, 201702
Need final combined table as :
LOB , Code, Code_Desc, Original_Payment,Original_Payment, Current_Payment, 201701,Original_Payment, Current_Payment, 201702
and so on ... for 12 month each year.
Which SAS PROC or method would be use to solve this.
Thank you
First, post test data in the form of a datastep.
Seoncd, your limited required output is not enough, variables cannot be called 201701, its invalid, and you can't have multiples of the same.
Finally, what you want is either a) two transposes - i.e. one for original payment, one for current payment, then merge the tow together, or b) use arrays.
Thank you.
The column name 201701 becasue previous step PROC Tranpose ID Month.
I shoudl rename Month ?
But it will have 12 month data .....
I would suggest using the PREFIX option within PROC TRANSPOSE to add a prefix so the variable becomes
Y201701 instead of _201701
You can also use IDLABEL if you want. Create another variable that has the date as January 2017 and then assign that to the IDLABEL.
Data Set:
LOB, Category, Code, Code_Desc, Original_Payable, Current _Payable, Month,
Month
201701,
201702,
I have tried to ranspose
ID monht.
It would not working, error message. have to create data set by month then merge or combine.
Absolutely, prefix the columns name.
What is not working? You should be able to do:
proc sort data=have; by lob category code code_desc month; run; proc transpose data=have out=original prefix=o; by lob category code code_desc; var original_payable; id month; run; proc transpose data=have out=current prefix=c; by lob category code code_desc; var current_payable; id month; run; data want; merge original current; by lob category code code_desc; run;
Although I would really advise against using data in the column names. I would use idlabel and put the year/month information in the label, then call your variables origX, and currX, X being incremental, that way you can refer to them using shorthand such as:
max(of orig:)
Or by arrays.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.