topic Re: PROC TRANSPOSE in SAS Programming

PROC TRANSPOSE

JHE — Fri, 26 May 2017 19:00:34 GMT

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

Re: PROC TRANSPOSE

Astounding — Fri, 26 May 2017 19:20:11 GMT

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?

Re: PROC TRANSPOSE

JHE — Fri, 26 May 2017 19:28:15 GMT

If I sort by Month, might be worked.

Let me try.

Re: PROC TRANSPOSE

JHE — Fri, 26 May 2017 19:31:56 GMT

Sort by month dse nto workign either...

not sure what should do.

Re: PROC TRANSPOSE

Astounding — Fri, 26 May 2017 19:36:04 GMT

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.

Re: PROC TRANSPOSE

JHE — Fri, 26 May 2017 19:41:21 GMT

ok, will do, thank you.