How to determine if there is no column within a dataset

[PaigeMiller]

We need to see the SASLOG with the following options enabled

OPTIONS SOURCE MPRINT MPRINTNEST NOSYMBOLGEN NOMLOGIC;

And also please explain in WORDS what is not working. You haven't done that either. We need both ... the word explanation and the SASLOG.

[Crubal]

Part of SASLOG: 

MLOGIC(NOSHOW_FCST): Beginning execution.
MPRINT(NOSHOW_FCST): OPTIONS NOTES SOURCE SOURCE2 MPRINT MLOGIC MERROR SYMBOLGEN;

I think it is enabled. 

'Check if Hist_5_6_&LOC_ID exists': 

Proc Sql noprint;
Select count(distinct name) into: does_column_exist
from sashelp.vcolumn where lowcase(name) = "noshow_cancel_pred_pos" and lowcase(memname) = "hist_5_6_pos_&loc_id";
Quit;

Log file for the part 'Check if Hist_5_6_&LOC_ID exists':

SYMBOLGEN: Macro variable DOES_COLUMN_EXIST resolves to 0
MLOGIC(NOSHOW_FCST): %IF condition &does_column_exist > 0 is FALSE
MLOGIC(NOSHOW_FCST): %DO loop index variable I is now 2; loop will iterate again.

However, dataset 'Hist_5_6_&LOC_ID' exists, as logfile mentions:

NOTE: The data set WORK.HIST_5_6_POS_AADAL has 912 observations and 9 variables.

So I would assume %IF condition &does_column_exist > 0 should be 'TRUE' instead of 'FALSE' in this way, and then proceed to obtain data 'Noshowfcst_&LOC_ID'. 

Thanks! 

[PaigeMiller]

You say the column noshow_cancel_pred_pos exists in SAS data set HIST_5_6_POS_AADAL, and SAS says it doesn't exist, because it doesn't find that column name in that data set.

So, I believe SAS.

Can you show us a screen capture that confirms the column of that name exists in that data set?

Also, I do not know why, but I am expecting a SASLOG that shows the PROC SQL code that we are discussing resolved into actual text via MPRINT and MPRINTNEST turned on, and you are not showing it to me.

[Crubal]

Hi,

The related information on MPRINT and MPRINTNEST are the following within log file:

MLOGIC(NOSHOW_FCST): Beginning execution.
MPRINT(NOSHOW_FCST): OPTIONS NOTES SOURCE SOURCE2 MPRINT MPRINTNEST MLOGIC MERROR SYMBOLGEN;

And the above is a screenshot for the dataset 'Hist_5_6_POS_AADAL'. 

And the last column is what I mentioned. Thank you! 

[PaigeMiller]

I'll keep trying until I see the SASLOG that I want to see.

When I run an example on my computer, I use this code

%macro dothis;
proc sql noprint;
	select count(distinct name) into :does_column_exist from sashelp.vcolumn where lowcase(libname)='sashelp' and 
		lowcase(memname)='class' and lowcase(name)='age';
quit;
%put &=does_column_exist;
%mend;

%dothis

and I get a SASLOG that looks like this:

1202  %dothis
MPRINT(DOTHIS):   proc sql noprint;
MPRINT(DOTHIS):   select count(distinct name) into :does_column_exist from sashelp.vcolumn where lowcase(libname)='sashelp' and
lowcase(memname)='class' and lowcase(name)='age';
MPRINT(DOTHIS):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


DOES_COLUMN_EXIST=       1

You haven't provided this part of the SASLOG, showing how the text is resolved into actual SAS code.

[Crubal]

I really appreciate you patience @PaigeMiller

The log file looks like:

NOTE: The data set WORK.HIST_5_6_POS_AADAL has 912 observations and 9 variables.
NOTE: PROCEDURE GENMOD used (Total process time):
      real time           0.14 seconds
      cpu time            0.09 seconds
      

MPRINT(NOSHOW_FCST):   Proc Sql noprint;
MPRINT(NOSHOW_FCST):   Select count(distinct name) into: does_column_exist from sashelp.vcolumn where lowcase(libname)='sashelp' 
and lowcase(name) = 'noshow_cancel_pred_pos' and lowcase(memname) = 'hist_5_6_pos_&loc_id';
MPRINT(NOSHOW_FCST):   Quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

MLOGIC(NOSHOW_FCST):  %PUT &=does_column_exist
SYMBOLGEN:  Macro variable DOES_COLUMN_EXIST resolves to        0
DOES_COLUMN_EXIST=       0
SYMBOLGEN:  Macro variable DOES_COLUMN_EXIST resolves to        0
MLOGIC(NOSHOW_FCST):  %IF condition &does_column_exist > 0 is FALSE
MLOGIC(NOSHOW_FCST):  %DO loop index variable I is now 2; loop will iterate again.

And in your example, is 'class' for dataset name and 'age' for variable name? 

Thank yoU! 

[PaigeMiller]

You need to use double quotes in your PROC SQL.

[Crubal]

Please see double quotes:

NOTE: The data set WORK.HIST_5_6_POS_AADAL has 912 observations and 9 variables.
NOTE: PROCEDURE GENMOD used (Total process time):
      real time           0.15 seconds
      cpu time            0.10 seconds
      

MPRINT(NOSHOW_FCST):   Proc Sql noprint;
SYMBOLGEN:  Macro variable LOC_ID resolves to AADAL
MPRINT(NOSHOW_FCST):   Select count(distinct name) into: does_column_exist from sashelp.vcolumn where lowcase(libname)="sashelp" 
and lowcase(name) = "noshow_cancel_pred_pos" and lowcase(memname) = "hist_5_6_pos_AADAL";
MPRINT(NOSHOW_FCST):   Quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.02 seconds
      

MLOGIC(NOSHOW_FCST):  %PUT &=does_column_exist
SYMBOLGEN:  Macro variable DOES_COLUMN_EXIST resolves to        0
DOES_COLUMN_EXIST=       0
SYMBOLGEN:  Macro variable DOES_COLUMN_EXIST resolves to        0
MLOGIC(NOSHOW_FCST):  %IF condition &does_column_exist > 0 is FALSE
MLOGIC(NOSHOW_FCST):  %DO loop index variable I is now 2; loop will iterate again.

[PaigeMiller]

This is a case (no pun intended) of having the wrong case, which is something we discussed earlier.

Proc Sql noprint;
    Select count(distinct name) into: does_column_exist
    from sashelp.vcolumn where lowcase(name) = "noshow_cancel_pred_pos" and 
    lowcase(memname) = "hist_5_6_pos_%lowcase(&loc_id)";
Quit;

[Crubal]

Finally solved this time, really appreciate your help @PaigeMiller

[PaigeMiller]

---

@RW9 wrote:

Check the metadata:

data _null_;
  set sashelp.vcolumn (where=(libname="<your lib>" and memname="<your dataset>"));
  if _n_=1 then call execute('%macro CodeToExecute;');
run;

This will only call the CodeToExecute macro if in <your lib>.<your dataset> (all in capitals in this case) contains 1 or more columns.

---

Shouldn't that say

if _n_=1 then call execute('%CodeToExecute;');

where the macro CodeToExecute has already been defined?

[RW9]

Yes, indeed.  Problem with writing code your not running.   Could of course be any valid SAS code, doesn't need to be a macro call.