Creating A Computed Column

[NewToAllThis]

Hi, I am trying to create a computed column that will return the value of a selected column unless it contains a specific value ‘n’ . In the case of ‘n’, I want it to return the value in another column in the same data set. I have tried to use the IFC function but I am getting an error message saying it needs to be a numeric value which ‘n’ is not. Appreciate any help, fairly new to this.
Thanks in advance.

[Kurt_Bremser]

Please post the complete log from that step. Use the </> button to open a window into which you copy/paste the log text.

[NewToAllThis]

1                                                          The SAS System                          16:01 Thursday, November 12, 2020

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          PROC SQL NOEXEC;
4             SELECT t1.G1ZWCG,
5                    t1.G1UEDT LABEL="FIP Created Date" AS G1UEDT,
6                    t1.G1PDVC LABEL="FIP Plan Amount" AS G1PDVC,
7                    t1.G1AAVN LABEL="FIP Created By" AS G1AAVN,
8                    t1.G1PZTA LABEL="WEBCOLLS Created By" AS G1PZTA,
9                    t1.n9e5cd LABEL="Router Number" AS n9e5cd,
10                   /* Total Created By */
11                     (IFC(t1.G1AAVN, 'WEBCOLLS', t1.G1PZTA, t1.G1AAVN)) AS 'Total Created By'n
12               FROM DM_DATA.DM_G1_FLEXISTPLAN_HEADER t1
13               WHERE t1.G1UEDT >= 1201111 AND t1.G1ZWCG NOT IS MISSING;
ERROR: Function IFC requires a numeric expression as argument 1.
14         QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      Memory                            446k
      OS Memory                         18728k
      Timestamp            11/12/2020  6:47:48 PM
      Page Faults                       0
      Page Reclaims                     32
      Page Swaps                        0
      Voluntary Context Switches        11
      Involuntary Context Switches      6
      Block Input Operations            0
      Block Output Operations           0
      
15         QUIT; RUN;
16         

[Kurt_Bremser]

IFC needs three arguments, and can accept a fourth. The first expression is a logical expression. Logical values in SAS are numeric. Most of the time this first expression is a condition you would otherwise use in an IF statement.

You cannot use a character variable there, but you can use a character variable in a condition:

IFC(t1.G1AAVN ne "", 'WEBCOLLS', t1.G1PZTA, t1.G1AAVN)

Note that the fourth argument here is irrelevant, as the condition will never result in a missing value.

[NewToAllThis]

This populated the new column with ‘WEBCOLLS’ for every row and not the value of G1PZTA 😞

[Kurt_Bremser]

Under which condition should the target variable be populated with which value?

[NewToAllThis]

If G1AAVN=“WEBCOLLS’ I want the new column to use the value in G1PZTA instead. For anything other than ‘WEBCOLLS’ I want the new column to keep the value of G1AAVN.

[Kurt_Bremser · Accepted solution]

Then this is your function call:

IFC(t1.G1AAVN = 'WEBCOLLS', t1.G1PZTA, t1.G1AAVN)

The first argument is the condition, the second the "then" value, the third the "else" value.

[NewToAllThis]

Thanks so much. Appreciate all your help. Really new at this 🙂

[Kurt_Bremser]

PS do NOT use those stupid name literals. Use labels for fancy strings, and valid SAS names for your variable names.

[PGStats]

Try function IFN instead of IFC.

[NewToAllThis]

I tried both IFN and IFC but getting errors on both unfortunately 😞

[PGStats]

What is the type (numeric or character) of variables t1.G1PZTA, t1.G1AAVN ?

[NewToAllThis]

Both character