topic Re: Create new variable if don't exists in SAS Programming

Create new variable if don't exists

Babloo — Tue, 17 Sep 2019 11:37:06 GMT

I'm executing the following code in most of the programs.. For the programs which don't have ENTITY_ID from the querying table, I want to create the variable ENTITY_ID (character) and assign the missing value instead of receiving the error 'ERROR: The following columns were not found in the contributing tables: ENTITY_ID. '

/*extract unique entity_id*/
proc sql;
create table dis_entity_id as select distinct ENTITY_ID format=$ENTITY. from &library..&Input_table.;
quit;

Re: Create new variable if don't exists

novinosrin — Tue, 17 Sep 2019 11:51:09 GMT

/*extract unique entity_id*/
proc sql;
create table dis_entity_id as 
select *,' ' as  ENTITY_ID 
from &library..&Input_table.;
quit

Re: Create new variable if don't exists

Babloo — Tue, 17 Sep 2019 11:59:44 GMT

Well, if the data is available for ENTITY_ID from the querying table then
I have to extract that value and not the missing value.

So If the variable exists then extract that value else assign missing value
by creating that variable.

Re: Create new variable if don't exists

novinosrin — Tue, 17 Sep 2019 12:02:41 GMT

Okay @Babloo Use this

https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Check-if-a-variable-exists-in-a-dataset/td-p/475813

In the respective IF THEN ELSE, plug in your SQL code

Re: Create new variable if don't exists

novinosrin — Tue, 17 Sep 2019 12:05:35 GMT

So, this is how you plug your sql in that macro


 %if %sysfunc(varnum(&dsid, &var)) > 0 %then %do;
       /*extract unique entity_id*/
proc sql;
create table dis_entity_id as 
select distinct  ENTITY_ID 
from &library..&Input_table.;
quit;
    %end;
    %else %do;
       proc sql;
create table dis_entity_id as 
select *,' '  ENTITY_ID 
from &library..&Input_table.;
quit;
    %end;

Re: Create new variable if don't exists

Babloo — Tue, 17 Sep 2019 12:09:44 GMT

I have to wrap your code with macro as you started the code with IF?

Re: Create new variable if don't exists

novinosrin — Tue, 17 Sep 2019 12:16:00 GMT

Yes, please read the comments, you will understand.

Basically, first, these are steps

1. Check if the variable already exists

2. If exists, run a plain extract query

3. If not, assign a new variable with that name with "missing" value

Now, using the above 3, think how can we translate that into SAS syntax. Gradually, you will build the code. Try

Re: Create new variable if don't exists

Tom — Tue, 17 Sep 2019 12:50:18 GMT

You might want to just use normal SAS code instead of SQL.

data dis_entity_id ;
  set &library..&input_table ;
  format ENTITY_ID $ENTITY. ;
  keep entity_id;
run;

proc sort data=dis_entity_id nodupkey;
  by entity;
run;

Re: Create new variable if don't exists

Quentin — Tue, 17 Sep 2019 12:55:10 GMT

One way would be to make a view which creates the variable. Something like below should work, if you don't mind side effect of changing the order of variables:

data vInputTable/view=vInputTable ;
  length Entity_ID $8 ;
  call missing(Entity_ID) ;
  set &library..&Input_table ;
run ;

Then read that view in your PROC SQL step.

It's often easier to fix data structures rather than code around them.

Re: Create new variable if don't exists

Babloo — Tue, 17 Sep 2019 13:09:30 GMT

Proc sort ending with error if there is no value for ENTITY_ID. Yes, it is right, but I'd like tweak the sort code to work only if there is any Observation from the dataset 'dis_entity_id'. Your proposed Data step works well for both the cases.

33         proc sort data=dis_entity_id nodupkey;
34           by entity;
ERROR: Variable ENTITY not found.
35         run;

Re: Create new variable if don't exists

Tom — Tue, 17 Sep 2019 13:15:01 GMT

Make sure to use the right variable name in the BY statement.

Re: Create new variable if don't exists

s_lassen — Tue, 17 Sep 2019 13:31:22 GMT

You can check if the column exist with SQL, e.g.

proc sql noprint;
  %let name=' ' as ENTITY_ID;
  select name into :name from
  dictionary.columns 
  where libname="%upcase(&library)" and memname="%upcase(&Input_table)" and name='ENTITY_ID';
  create table dis_entity_id as select distinct &name format=$ENTITY. from &library..&Input_table.;
quit;

It's a bit fast and dirty, but I think it should work. The idea is that if the column is not found, the macro variable NAME will not be overwritten.

Re: Create new variable if don't exists

Quentin — Tue, 17 Sep 2019 13:36:10 GMT

You don't mind the uninitialized note thrown by:

data dis_entity_id ;
  set &library..&input_table ;
  format ENTITY_ID $ENTITY. ;
  keep entity_id;
run;

Generally I tend to consider that an error message. (Actually, I tend to make it an error message with VARINITCHK=error).

Re: Create new variable if don't exists

Tom — Tue, 17 Sep 2019 13:48:14 GMT

@Quentin wrote:

You don't mind the uninitialized note thrown by:
data dis_entity_id ;
  set &library..&input_table ;
  format ENTITY_ID $ENTITY. ;
  keep entity_id;
run;
?

Generally I tend to consider that an error message. (Actually, I tend to make it an error message with VARINITCHK=error).

Nope. It is useful information. It lets us know that ENTITY_ID did not exist in the source data.