Desktop productivity for business analysts and programmers

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

Reply
Not applicable
Posts: 0

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

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.
Trusted Advisor
Posts: 2,127

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

Posted in reply to deleted_user
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.
Not applicable
Posts: 0

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

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.
Ask a Question
Discussion stats
  • 2 replies
  • 183 views
  • 0 likes
  • 2 in conversation