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
