topic Re: Creating A Computed Column in New SAS User

Creating A Computed Column

NewToAllThis — Thu, 12 Nov 2020 18:36:26 GMT

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.

Re: Creating A Computed Column

Kurt_Bremser — Thu, 12 Nov 2020 18:46:21 GMT

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

Re: Creating A Computed Column

NewToAllThis — Thu, 12 Nov 2020 18:55:14 GMT

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

Re: Creating A Computed Column

PGStats — Thu, 12 Nov 2020 19:02:53 GMT

Try function IFN instead of IFC.

Re: Creating A Computed Column

NewToAllThis — Thu, 12 Nov 2020 19:39:49 GMT

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

Re: Creating A Computed Column

Kurt_Bremser — Thu, 12 Nov 2020 19:58:59 GMT

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.

Re: Creating A Computed Column

PGStats — Thu, 12 Nov 2020 19:59:33 GMT

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

Re: Creating A Computed Column

NewToAllThis — Thu, 12 Nov 2020 20:05:53 GMT

Both character

Re: Creating A Computed Column

Kurt_Bremser — Thu, 12 Nov 2020 20:09:19 GMT

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

Re: Creating A Computed Column

NewToAllThis — Thu, 12 Nov 2020 20:26:21 GMT

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

Re: Creating A Computed Column

Kurt_Bremser — Thu, 12 Nov 2020 20:28:57 GMT

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

Re: Creating A Computed Column

NewToAllThis — Thu, 12 Nov 2020 20:31:10 GMT

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.

Re: Creating A Computed Column

Kurt_Bremser — Thu, 12 Nov 2020 21:22:45 GMT

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.

Re: Creating A Computed Column

NewToAllThis — Thu, 12 Nov 2020 21:28:39 GMT

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