BookmarkSubscribeRSS Feed
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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

 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

Thank you.

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

I shoudl rename Month ?

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

Reeza
Super User

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. 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

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.

 

 

 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

Absolutely, prefix the columns name.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 921 views
  • 0 likes
  • 3 in conversation