DATA Step, Macro, Functions and more

SAS data set combine

Reply
Frequent Contributor
Frequent Contributor
Posts: 79

SAS data set combine

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

 

Super User
Super User
Posts: 7,392

Re: SAS data set combine

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.

 

Frequent Contributor
Frequent Contributor
Posts: 79

Re: SAS data set combine

Thank you.

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

I shoudl rename Month ?

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

Super User
Posts: 17,750

Re: SAS data set combine

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. 

Frequent Contributor
Frequent Contributor
Posts: 79

Re: SAS data set combine

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.

 

 

 

Frequent Contributor
Frequent Contributor
Posts: 79

Re: SAS data set combine

Absolutely, prefix the columns name.

Super User
Super User
Posts: 7,392

Re: SAS data set combine

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 origSmiley Happy

Or by arrays.

Ask a Question
Discussion stats
  • 6 replies
  • 118 views
  • 0 likes
  • 3 in conversation