topic Re: Create a new column with values of label of a varaible in SAS Programming

Create a new column with values of label of a varaible

Ronein — Tue, 15 Jun 2021 20:16:10 GMT

Hello,

raw_tbl contain the following columns:

ID (label: Customer ID)

X (label: Indicator good/bad credit card user)

w (label: Indicator good/bad loans taker)

I want to create a summary table and add a column called label_of_Var that will get the value of label of Var X?

What is the way to do it please?

In this example I wrote it manually "Indicator good/bad credit card user" as label_of_Var

But my question is how to create a code that put automatically label of X as a value of column label_of_Var?

proc sql;
Create table wanted_x as
select  "Ind_X" as Var,
            "Indicator good/bad credit card user" as label_of_Var,
           case when Ind_X=1 then 'Fail' else 'Pass' end as Ind_pass_Fail,
          count(*) as nr,
         calculated nr/(select count(*) from raw_tbl) as pct
From  raw_tbl
group by calculated Ind_pass_Fail;
quit;

Re: Create a new column with values of label of a varaible

ballardw — Tue, 15 Jun 2021 20:42:21 GMT

The functions that would allow this are not supported in Proc SQL.

So you may try

1) Select the label from the Dictionary.columns for the variable X for that source data set and join it to this table

2) Select the label from the dictionary.columns into a macro variable and use that as the value

Re: Create a new column with values of label of a varaible

andreas_lds — Wed, 16 Jun 2021 06:07:15 GMT

Unfortunately the function vlabel is only available in a data step, not in proc sql.

Re: Create a new column with values of label of a varaible

Kurt_Bremser — Wed, 16 Jun 2021 06:15:46 GMT

Use macro variables:

%let varname=Ind_X;

proc sql noprint;
select label into :varlabel
from dictionary.columns
where libname = "WORK" and memname = "RAW_TBL" and upcase(name) = upcase("&varname.")
;
create table wanted_x as
  select
    "&varname." as Var,
    "&varlabel." as label_of_Var,
    case when Ind_X=1 then 'Fail' else 'Pass' end as Ind_pass_Fail,
    count(*) as nr,
    calculated nr/(select count(*) from raw_tbl) as pct
  from raw_tbl
  group by calculated Ind_pass_Fail
;
quit;