DATA Step, Macro, Functions and more

PROC TRANSPOSE

Reply
Frequent Contributor
Frequent Contributor
Posts: 98

PROC TRANSPOSE

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

Super User
Posts: 5,516

Re: PROC TRANSPOSE

So you sort the data set BY LOB.  What makes you think it would be in order:

 

BY LOB Category Code CODE_DESCRIPTION ORIGINAL_PAYABLE TOTAL_CURRENT_PAYABLE;

 

Did you mean to include a VAR statement in PROC TRANSPOSE?

Frequent Contributor
Frequent Contributor
Posts: 98

Re: PROC TRANSPOSE

Posted in reply to Astounding

If I sort by Month, might be worked.

 

Let me try.

 

 

Frequent Contributor
Frequent Contributor
Posts: 98

Re: PROC TRANSPOSE

Sort by month dse nto workign either...

 

not sure what should do.

 

 

Super User
Posts: 5,516

Re: PROC TRANSPOSE

Well, you're doing the right thing.  You will have to fiddle with the code until you get something that works.  Just one suggestion:  get rid of all the macro language entirely.  Hard-code everything.  That gives you a chance of figuring out what the error messages mean.  Once you have a program that works, then you can worry about converting it to a macro.

Frequent Contributor
Frequent Contributor
Posts: 98

Re: PROC TRANSPOSE

Posted in reply to Astounding

ok, will do, thank you.

Ask a Question
Discussion stats
  • 5 replies
  • 253 views
  • 2 likes
  • 2 in conversation