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   ... View more

I run the SAS code: %macro LOBCode(lobname1,lobname2); PROC SQL feedback; /*CREATE TABLE Paid_Claims_Final&lobname1 AS */ CREATE TABLE Paid_Final&lobname1 AS SELECT DISTINCT CODE.LOB, CODE.Category, TEMP.Code AS Code, TEMP.CODE_DESCRIPTION, SUM(TEMP.Current_Payable) FORMAT=dollarx10.2 AS TOTAL_CURRENT_PAYABLE, SUM(TEMP.Original_Payable) FORMAT=dollarx10.2 AS ORIGINAL_PAYABLE, COUNT(TEMP.CLCL_ID) AS TOTAL, STRIP(temp.MONTH) AS MONTH FROM PAYMNT&lobname1 TEMP INNER JOIN REWORKI.CODE CODE on Code.Code=TEMP.CODE GROUP BY CODE.LOB,CODE.Category,TEMP.CODE, TEMP.CODE_DESCRIPTION, STRIP(temp.MONTH) HAVING TEMP.Rework_Claim=1 AND CODE.LOB=&lobname2; QUIT; /************************************************************/ Proc Sort data=work.Paid_Final&lobname1 out=work.Paid_FinalSort&lobname1; by LOB; run; /****************************************************************************/ PROC TRANSPOSE DATA=Paid_FinalSort&lobname1 OUT=Paid_Final&lobname1.TRANS (DROP=_NAME_); ID MONTH; BY LOB Category Code CODE_DESCRIPTION ORIGINAL_PAYABLE TOTAL_CURRENT_PAYABLE; RUN; %mend LOBCode;   Go tthe error message: ERROR: Data set WORK.PAID_FINALSORT1300 is not sorted in ascending sequence. The current BY group has ORIGINAL_PAYABLE = $1.841,67 and the next BY group has ORIGINAL_PAYABLE = $49,49. %LOBCode(1300,'1300');   Please see attach log file.   Thank ... View more

My SAS code: libname ReworkI "/export/home/qh19919/INPUT/Rework"; libname ReworkO "/export/home/qh19919/OUTPUT/Rework"; /*************************************************************************************/ %macro LOBNoCode(lobname1,lobname2); PROC SQL feedback; CREATE TABLE Paid_Claims_Final&lobname1 AS SELECT DISTINCT CODE.LOB, CODE.Category, TEMP.CLCL_EOB_EXCD_ID AS Code, TEMP.EXCD_LONG_TEXT1 AS CODE_DESCRIPTION, COUNT(TEMP.CLCL_ID) AS TOTAL, STRIP(PUT(DATEPART(TEMP.MONTH),YYMMn6.)) as MONTH FROM work.SQL_TEMP TEMP INNER JOIN REWORKI.CODE CODE on Code.Code=TEMP.CLCL_EOB_EXCD_ID GROUP BY CODE.LOB,CODE.Category,TEMP.CLCL_EOB_EXCD_ID,TEMP.EXCD_LONG_TEXT1, STRIP(PUT(DATEPART(TEMP.MONTH),YYMMD.)) HAVING TEMP.Rework_Claim=1 AND CODE.LOB=&lobname2; QUIT; /****************************************************************************/ PROC TRANSPOSE DATA=Paid_Claims_Final&lobname1 OUT=Paid_Claims_Final&lobname1.TRANS (DROP=_NAME_); ID MONTH; BY LOB Category Code CODE_DESCRIPTION; RUN; %mend LOBNoCode; %LOBNoCode(0100,'0100'); %LOBNoCode(0500,'0500'; libname ReworkI "/export/home/qh19919/INPUT/Rework"; libname ReworkO "/export/home/qh19919/OUTPUT/Rework"; /*************************************************************************************/ %macro LOBNoCode(lobname1,lobname2); PROC SQL feedback; CREATE TABLE Paid_Claims_Final&lobname1 AS SELECT DISTINCT CODE.LOB, CODE.Category, TEMP.CLCL_EOB_EXCD_ID AS Code, TEMP.EXCD_LONG_TEXT1 AS CODE_DESCRIPTION, COUNT(TEMP.CLCL_ID) AS TOTAL, STRIP(PUT(DATEPART(TEMP.MONTH),YYMMn6.)) as MONTH FROM work.SQL_TEMP TEMP INNER JOIN REWORKI.CODE CODE on Code.Code=TEMP.CLCL_EOB_EXCD_ID GROUP BY CODE.LOB,CODE.Category,TEMP.CLCL_EOB_EXCD_ID,TEMP.EXCD_LONG_TEXT1, STRIP(PUT(DATEPART(TEMP.MONTH),YYMMD.)) HAVING TEMP.Rework_Claim=1 AND CODE.LOB=&lobname2; QUIT; /****************************************************************************/ PROC TRANSPOSE DATA=Paid_Claims_Final&lobname1 OUT=Paid_Claims_Final&lobname1.TRANS (DROP=_NAME_); ID MONTH; BY LOB Category Code CODE_DESCRIPTION; RUN; %mend LOBNoCode; %LOBNoCode(0100,'0100'); %LOBNoCode(0500,'0500';   It only created 0100 relate table and proc transpose. stopped at 0500, not sure why .   log: 28 %macro LOBNoCode(lobname1,lobname2); 29 30 PROC SQL feedback; 31 CREATE TABLE Paid_Claims_Final&lobname1 AS 32 SELECT DISTINCT 33 CODE.LOB, 34 CODE.Category, 35 TEMP.CLCL_EOB_EXCD_ID AS Code, 36 TEMP.EXCD_LONG_TEXT1 AS CODE_DESCRIPTION, 37 COUNT(TEMP.CLCL_ID) AS TOTAL, 38 STRIP(PUT(DATEPART(TEMP.MONTH),YYMMn6.)) as MONTH 39 FROM work.SQL_TEMP TEMP 40 INNER JOIN REWORKI.CODE CODE on Code.Code=TEMP.CLCL_EOB_EXCD_ID 41 GROUP BY CODE.LOB,CODE.Category,TEMP.CLCL_EOB_EXCD_ID,TEMP.EXCD_LONG_TEXT1, 42 STRIP(PUT(DATEPART(TEMP.MONTH),YYMMD.)) 43 HAVING TEMP.Rework_Claim=1 AND CODE.LOB=&lobname2; 44 QUIT; 45 /****************************************************************************/ 46 PROC TRANSPOSE DATA=Paid_Claims_Final&lobname1 OUT=Paid_Claims_Final&lobname1.TRANS (DROP=_NAME_); 47 ID MONTH; 48 BY LOB Category Code CODE_DESCRIPTION; 49 RUN; 50 51 %mend LOBNoCode; 2 The SAS System 08:43 Friday, May 19, 2017 52 %LOBNoCode(0100,'0100'); NOTE: Statement transforms to: select distinct CODE.LOB, CODE.Category, TEMP.Code, TEMP.CODE_DESCRIPTION, COUNT(TEMP.CLCL_ID) as TOTAL, STRIP(PUT(DATEPART(TEMP.MONTH), YYMMN6.0)) as MONTH from WORK.SQL_TEMP TEMP, REWORKI.CODE CODE where CODE.Code = TEMP.CLCL_EOB_EXCD_ID group by CODE.LOB, CODE.Category, TEMP.CLCL_EOB_EXCD_ID, TEMP.EXCD_LONG_TEXT1, STRIP(PUT(DATEPART(TEMP.MONTH), YYMMD7.0)) having (TEMP.Rework_Claim = 1) and (CODE.LOB = '0100'); NOTE: The query requires remerging summary statistics back with the original data. NOTE: Compressing data set WORK.PAID_CLAIMS_FINAL0100 increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: Table WORK.PAID_CLAIMS_FINAL0100 created, with 209 rows and 6 columns. NOTE: PROCEDURE SQL used (Total process time): real time 15:05.13 cpu time 57.07 seconds   　 NOTE: There were 209 observations read from the data set WORK.PAID_CLAIMS_FINAL0100. NOTE: The data set WORK.PAID_CLAIMS_FINAL0100TRANS has 209 observations and 5 variables. NOTE: Compressing data set WORK.PAID_CLAIMS_FINAL0100TRANS increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.13 seconds cpu time 0.00 seconds   53 %LOBNoCode(0500,'0500'; 54 55 GOPTIONS NOACCESSIBLE; 56 %LET _CLIENTTASKLABEL=; 57 %LET _CLIENTPROJECTPATH=; 58 %LET _CLIENTPROJECTNAME=; 59 %LET _SASPROGRAMFILE=; 60 61 ;*';*";*/;quit;run; 62 ODS _ALL_ CLOSE; 63 64 65 QUIT; RUN; 66   ... View more