topic Re: Detect if Column Exists in a Table in SAS Programming

Detect if Column Exists in a Table

bobsas1 — Wed, 15 Jun 2022 15:36:25 GMT

There are tables for different dates where the column name changed for some reason. I'm trying to: 1). detect if the column of a specific name exists in the table. 2). If it exists in the table, then in PROC SQL SELECT it will use that column name. If the specified column name does not exist in the table (i.e., it has a slightly different naming convention), then I want PROC SQL SELECT to use an alternative column name that I specify in advance (it appears that only two different naming conventions were used for the subject column).

The code I am testing to identify a specified missing column (item #1) was taken from a different SAS Community topic, but it doesn't appear to work properly:

data tbl1;

input ID x1,x2 ;

cards;

1 10 15

2 20 25

3 30 35

4 40 45

5 50 55

;

run;

proc sql noprint;

select count(*) into :varexist

from dictionary.columns

where libname="WORK" and memname="TBL1" and upcase(name)="X3";

quit;

data tbl2;

set tbl1;

if not &varexist then x3=.;

run;

If I use upcase(name)="X3" or upcase(name)="X2", a column X3 is created with "." values.

Thoughts on how item #1 and #2 can be accomplished?

Re: Detect if Column Exists in a Table

PaigeMiller — Wed, 15 Jun 2022 15:12:19 GMT

I will leave it up to you to fix the typos in your code.

Before PROC SQL, you need to add the following:

%let varlist=0;

From now on, please paste code into the box that appears when you click on the "little running man" icon.

Re: Detect if Column Exists in a Table

tarheel13 — Wed, 15 Jun 2022 15:15:56 GMT

https://communities.sas.com/t5/SAS-Programming/SAS-query/m-p/350433#M81457

here is a topic that shows how to check if a variable exists in a dataset. you should be able to modify that to do what you want when the variable does not exist.

Re: Detect if Column Exists in a Table

ballardw — Wed, 15 Jun 2022 15:24:55 GMT

When I clean up your code and run the Proc SQL adding this:

20   %put Varexist is: &varexist;
Varexist is:        0

The result for Varexist is zero as shown.

So in the following data step you have submitted:

data tbl2;
   set tbl1;
   if not 0 then x3=.;
run;

So I am not sure what you are actually attempting here. "Not 0" is always true in SAS: 0 is treated as false so "not false"=true.

Personally, I would go back to when the data is read into SAS to see why the variable names are different and address the issue at that point. One suspects Proc Import is involved.

I have run into this where the data source changes column headings in source files but since I read them with data steps then as long as the values are in the correct column and of the appropriate values then no problems. Plus the data steps generally throw data errors when the column order gets changed (they do that to).

Or us Proc Datasets to change the name of the variable in those sets since you say there seem to be two names.

Re: Detect if Column Exists in a Table

Reeza — Wed, 15 Jun 2022 15:37:39 GMT

Code works fine once you fix all the syntax errors.

data tbl1;
input ID x1 x2 ;
cards;
1 10 15
2 20 25
3 30 35
4 40 45
5 50 55
;

run;

proc sql noprint;
select count(*) into :varexist
from dictionary.columns
where libname="WORK" and memname="TBL1" and upcase(name)="X3";

quit;

%put &varexist;


data tbl2;

set tbl1;

if not &varexist then x3=.;

run;