topic Re: SAS data set combine in SAS Programming

SAS data set combine

JHE — Wed, 31 May 2017 13:02:23 GMT

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

Re: SAS data set combine

RW9 — Wed, 31 May 2017 13:06:40 GMT

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.

Re: SAS data set combine

JHE — Wed, 31 May 2017 13:22:07 GMT

Thank you.

The column name 201701 becasue previous step PROC Tranpose ID Month.

I shoudl rename Month ?

But it will have 12 month data .....

Re: SAS data set combine

Reeza — Wed, 31 May 2017 13:28:59 GMT

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.

Re: SAS data set combine

JHE — Wed, 31 May 2017 13:38:36 GMT

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.

Re: SAS data set combine

JHE — Wed, 31 May 2017 13:40:08 GMT

Absolutely, prefix the columns name.

Re: SAS data set combine

RW9 — Wed, 31 May 2017 14:19:03 GMT

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.