making proc sql create table info as using a call execute

[alepage]

Hello,

I have created a SAS dataset which tell me the path, fname and engine to use read the dataset via a call execute.

The issue, I am facing is that in some dataset the agreement number this value is under the variable police while in other one we have  they are under agreement_nbr.

proc sql;
create table info as
select police as agreement_nbr
from source
Quit;
 and in some other case we will have
proc sql;
create table info as
select agreement_nbr 
from source
Quit;

does a coalesce statement will be good with any warning ?

ex 

proc sql;

create table info as

select coalesce(police, agreement_nbr) as agreement_nbr

from source;

quit;

quit;

[Tom]

Only if both variables exist.  SQL statements will throw an error if you try to reference a variable that does not exist.

Why are you using SQL?  Why not use normal SAS code?  A data step will create a variable for you if you reference it in the code and it was not already created by the source data.

data info ;
  set source;
  agreement_nbr = coalesce(agreement_nbr,police);
  keep agreement_nbr;
run;

If the variables are ( or should be) character then you will need to use the COALESCEC() function instead.  In that case you might want to also use the CATS() function so it can handle datasets where POLICE or AGREEMENT_NBR are character.

data info ;
  set source;
  length newvar $40;
  newvar = coalescec(cats(agreement_nbr),cats(police));
  keep newvar;
  rname newvar=agreement_nbr;
run;

[alepage]

Hello, I am using a proc sql because I need to make an inner join from a policies list table.  So, imagine that police is the variable name into some datasets and for other dataset it will be agreement_nbr, does the coalescec will work or not?  If not, what will be a work around solution.

[Tom]

---

@alepage wrote:

Hello, I am using a proc sql because I need to make an inner join from a policies list table.  So, imagine that police is the variable name into some datasets and for other dataset it will be agreement_nbr, does the coalescec will work or not?  If not, what will be a work around solution.

---

Know what variable to use before you write the code.

Or write some code that can check and then generate the right code based on which variable(s) exist.  Perhaps a macro, but also you could do it with CALL EXECUTE() if you wanted, just like you mentioned in your topic.