topic Re: Proc SQL Create Table - creating a new column by adding several columns in SAS Enterprise Guide

Proc SQL Create Table - creating a new column by adding several columns

deleted_user — Wed, 08 Sep 2010 18:30:17 GMT

I have been given an EG process flow to maintain, and one of the code segments creates a work table by formatting character data from an input file. I need to create a new column in this table by adding 4 dollar fields to get the value for the new column.

I don't get any errors, but I don't get any output in the new column either (data column shows '.'). My code is:

(Input(put(&importfilename..Disb_1_Net_Amt,$8.),17.2)) FORMAT=DOLLAR17.2 AS Disb_1_Net_Amount,
(Input(put(&importfilename..Disb_2_Net_Amt,$8.),17.2)) FORMAT=DOLLAR17.2 AS Disb_2_Net_Amount,
(Input(put(&importfilename..Disb_3_Net_Amt,$8.),17.2)) FORMAT=DOLLAR17.2 AS Disb_3_Net_Amount,
(Input(put(&importfilename..Disb_4_Net_Amt,$8.),17.2)) FORMAT=DOLLAR17.2 AS Disb_4_Net_Amount,

/* new column */
((CALCULATED Disb_1_Net_Amount) + (CALCULATED Disb_2_Net_Amount)
+ (CALCULATED Disb_3_Net_Amount) + (CALCULATED Disb_4_Net_Amount))
FORMAT=DOLLAR17.2 AS Total_Disb_Net_Amount,

What am I doing wrong? Thanks.

Re: Proc SQL Create Table - creating a new column by adding several columns

Doc_Duke — Wed, 08 Sep 2010 20:12:41 GMT

Does your query show data in the ...Net_Amount fields? Are they all there?

One possibility:
By using the "+" operator, if any of the component parts are missing, the result is missing. If you have missing pieces and the sum is still meaningful, use the SUM function to add them up.

Re: Proc SQL Create Table - creating a new column by adding several columns

deleted_user — Wed, 08 Sep 2010 20:56:20 GMT

That did it. Some of the Net Amount fields were null/blank '.'

The new code of

SUM(CALCULATED Disb_1_Net_Amount,CALCULATED Disb_2_Net_Amount, CALCULATED Disb_3_Net_Amount,CALCULATED Disb_4_Net_Amount) FORMAT=DOLLAR17.2 AS Total_Disb_Net_Amount,

works like a charm. Thank you.