DATA Step, Macro, Functions and more

%macro?

Reply
Frequent Contributor
Frequent Contributor
Posts: 98

%macro?

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

 

PROC Star
Posts: 7,474

Re: %macro?

You're missing a closing parenthesis in the line:

 %LOBNoCode(0500,'0500';

Shouldn't that be:

 %LOBNoCode(0500,'0500');

Art, CEO, AnalystFinder.com

 

Trusted Advisor
Posts: 1,918

Re: %macro?

You are missing a parenthesis.

 

This is the line missing the parenthesis:

%LOBNoCode(0500,'0500';
Frequent Contributor
Frequent Contributor
Posts: 98

Re: %macro?

Posted in reply to PaigeMiller

ohm yes, thank you .Smiley Happy

 

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