Desktop productivity for business analysts and programmers

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

Reply
N/A
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.
Valued Guide
Posts: 2,111

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

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.
N/A
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
  • 147 views
  • 0 likes
  • 2 in conversation