05-31-2017 09:02 AM
I have two tabes need to combine, for monthly payment , see atatch file format as refernce.
LOB , Code, Code_Desc, Original_Payment, Current_Payment, 201701
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.
05-31-2017 09:06 AM
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.
05-31-2017 09:28 AM
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.
05-31-2017 09:38 AM
LOB, Category, Code, Code_Desc, Original_Payable, Current _Payable, Month,
I have tried to ranspose
It would not working, error message. have to create data set by month then merge or combine.
05-31-2017 10:19 AM
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:
Or by arrays.