topic Re: How to determine if there is no column within a dataset in SAS Data Management

How to determine if there is no column within a dataset

Crubal — Fri, 28 Jul 2017 14:24:15 GMT

Hi,

I have a question within my sas macro function.

Some exceptional cases that Table A may not have any columns. And I would like the process going if it has columns, and stops when it does not have. How could I make it happen?

Thank you!

Re: How to determine if there is no column within a dataset

RW9 — Fri, 28 Jul 2017 14:41:51 GMT

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.

Re: How to determine if there is no column within a dataset

Crubal — Fri, 28 Jul 2017 15:18:13 GMT

Thanks for your reply @RW9

Sorry I didnot explain clearly. In my case, this part is within a macro process. For example, my pseudo code looks like:

%Macro ....

Obtain Table A from database.

Check if Table A has columns or not.

Run later process.

%Mend;

How can I realize ( Check if Table A has columns or not)

Thank you!

Re: How to determine if there is no column within a dataset

PaigeMiller — Fri, 28 Jul 2017 15:50:30 GMT

inside your macro

run some code

check to see if the column exists using the code provided by RW9, where the additional processing is what he calls CodeToExecute (which will execute only if the column exists)

end of macro

execute macro

ALTERNATIVE

Create a macro

run some code

check to see if the column exists

proc sql noprint;
    select count(distinct name) into :does_column_exist
   from sashelp.vcolumn where name='desired_column_name'
        and lib='your_library' and memname='your_data_set_name';
quit;
%if &does_column_exist>0 %then %do;

Note, capitalization is important in name='desired_column_name', so this can be avoided by

lowcase(name)='desired_column_name' in all lower case

rest of code

%end;

%mend;

Execute macro

Re: How to determine if there is no column within a dataset

PaigeMiller — Fri, 28 Jul 2017 15:43:48 GMT

@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?

Re: How to determine if there is no column within a dataset

RW9 — Fri, 28 Jul 2017 15:51:02 GMT

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.

Re: How to determine if there is no column within a dataset

Crubal — Fri, 28 Jul 2017 15:53:45 GMT

Thanks @PaigeMiller It works for me.

Re: How to determine if there is no column within a dataset

Crubal — Fri, 28 Jul 2017 19:55:25 GMT

Hi @PaigeMiller I tried as you suggested:

data _null_;
set sashelp.vcolumn (where=(libname = "<WORK>" and memname = "<Table A>"));
%if _n_ = 1 %then %do;

Proc Sql;
Create Table Table B as select distinct
...

...

from ...
Quit;

%end;

Run;

However, even though Table A has columns, the log file shows _n_ = 1 is false, and cannot do later sql procedure.

How could I modify it?

Thanks!

Re: How to determine if there is no column within a dataset

PaigeMiller — Fri, 28 Jul 2017 19:56:19 GMT

Show us your actual code (at least the relevant parts)

Re: How to determine if there is no column within a dataset

Crubal — Fri, 28 Jul 2017 20:06:13 GMT

Okay thanks.

The following obtains Table A_&LOC_ID. Where &LOC_ID differs by location.

proc genmod data=A0_&LOC_ID;
class Mon Dow;
by Operating_Div_cd chain_cd loc_id prod_class_id;
model Cancel_Noshow = Mon Dow/ dist=poisson
link=log scale=pearson;
output out=A_&LOC_ID p=Noshow_Cancel_Pred_Pos;
where Stay_Date between &START1 and &END1;
run;
ods exclude all;

The following checks if table A_&LOC_ID has any columns or not. And continue the process to get B_&LOC_ID.

/* Check if table A_&LOC_ID has any columns or not */

data _null_;
set sashelp.vcolumn (where=(libname = "<WORK>" and memname = "<HIST_5_6_Pos_&LOC_ID>"));
%if _n_ = 1 %then %do;

/* Continue the process when _n_ = 1 */
Proc Sql;
Create Table B_&LOC_ID as select distinct 
Operating_Div_Cd,
Chain_Cd,
Loc_Id,
Prod_Class_Id,
Mon,
Dow,
Case 
When Weekday(Stay_Date) in (1,2,3,4,5) then 'WD'
Else 'WE'
End as DY_TYP,
avg(Noshow_Cancel_Pred_Pos) as Noshow_Fcst
from A_&LOC_ID
group by 1, 2, 3, 4, 5, 6, 7;
Quit;

%end;

Run;

Re: How to determine if there is no column within a dataset

RW9 — Fri, 28 Jul 2017 21:55:00 GMT

You have fallen into two common misconceptions:

1) What macro language is compared to Base SAS. Macro language, all those % and &'s, is a pre-processor, i.e. a limited scope text generation tool. If scans the text you present and using some simple logic expands the text out. This full text is then passed on to the Base SAS compiler which compiles and executes the script. You are using _n_ which is an automatic datastep variable in macro processing, which is not going to work. You would need to call execute, or put a value into a macro variable.

2) Having the thinking that you need to split data out and then loop over that data. You can simplify your code so much, and save yourself a lot of headaches is dropping this whole thinking. As an example, you are looping over the data blocking out certain items and then running some models. Another way of looking at it would be to add into the data another variable, a categorising variable, which holds this blocking information, then include that in the by group processing. That way you don't loop, but leave the procedure to handle all that. If you can provide some simple test data, I can show you, but for example, I could take sashelp.class and do a loop over that data based on gender, i.e. it would be %do i=1 % to 2;..., alternatively I could just add gender into the by group.

Re: How to determine if there is no column within a dataset

PaigeMiller — Fri, 28 Jul 2017 23:20:05 GMT

@Crubal wrote:

Okay thanks.

The following obtains Table A_&LOC_ID. Where &LOC_ID differs by location.

proc genmod data=A0_&LOC_ID;
class Mon Dow;
by Operating_Div_cd chain_cd loc_id prod_class_id;
model Cancel_Noshow = Mon Dow/ dist=poisson
link=log scale=pearson;
output out=A_&LOC_ID p=Noshow_Cancel_Pred_Pos;
where Stay_Date between &START1 and &END1;
run;
ods exclude all;

The following checks if table A_&LOC_ID has any columns or not. And continue the process to get B_&LOC_ID.

/* Check if table A_&LOC_ID has any columns or not */

data _null_;
set sashelp.vcolumn (where=(libname = "<WORK>" and memname = "<HIST_5_6_Pos_&LOC_ID>"));
%if _n_ = 1 %then %do;

/* Continue the process when _n_ = 1 */
Proc Sql;
Create Table B_&LOC_ID as select distinct 
Operating_Div_Cd,
Chain_Cd,
Loc_Id,
Prod_Class_Id,
Mon,
Dow,
Case 
When Weekday(Stay_Date) in (1,2,3,4,5) then 'WD'
Else 'WE'
End as DY_TYP,
avg(Noshow_Cancel_Pred_Pos) as Noshow_Fcst
from A_&LOC_ID
group by 1, 2, 3, 4, 5, 6, 7;
Quit;

%end;

Run;

Here's the code I suggested:

proc sql noprint;
    select count(distinct name) into :does_column_exist
   from sashelp.vcolumn where lowcase(name)='desired_column_name'
        and lowcase(lib)='your_library' and lowcase(memname)='your_data_set_name';
quit;
%if &does_column_exist>0 %then %do;

You haven't really done anything like this. Please note that where I have typed desired_column_name you should type the actual name of the variable in lower case, etc. etc. etc.

Also remember there needs to be an %end; statement somewhere below

Re: How to determine if there is no column within a dataset

Crubal — Mon, 31 Jul 2017 13:34:05 GMT

Hi @PaigeMiller

Thanks for your reply, and I tried yours in the very beginning, but result is same. Could you please let me know anything wrong with the following code? Thanks!

		proc genmod data=A0_&LOC_ID;
			class Mon Dow;
			by Operating_Div_cd chain_cd loc_id prod_class_id;
			model Cancel_Noshow = Mon Dow/ dist=poisson
			link=log scale=pearson;
			output out=A_&LOC_ID p=Noshow_Cancel_Pred_Pos;
			where Stay_Date between &START1 and &END1;
		run;
		ods exclude all;

/* Check if A_&LOC_ID has any columns or not */
Proc Sql noprint;
   Select count(distinct name) into: does_column_exist
      from sashelp.vcolumn where lowcase(name) = 'noshow_cancel_pred_pos' and lowcase(memname) = 'a_&loc_id';
Quit;

       /* Conduct the following part according to the condition */
	%if &does_column_exist > 0 %then %do;

		/* Gather output data by Month and day of week level for Noshow + Cancellation */
		Proc Sql;
			Create Table B_&LOC_ID as select distinct 
				Operating_Div_Cd,
				Chain_Cd,
				Loc_Id,
				Prod_Class_Id,
				Mon,
				Dow,
				Case 
					When Weekday(Stay_Date) in (1,2,3,4,5) then 'WD'
					Else 'WE'
				End	as DY_TYP,
				avg(Noshow_Cancel_Pred_Pos) as Noshow_Fcst
			from A_&LOC_ID
			group by 1, 2, 3, 4, 5, 6, 7;
		Quit;
		
		%end;

Re: How to determine if there is no column within a dataset

PaigeMiller — Mon, 31 Jul 2017 13:36:04 GMT

@Crubal wrote:

Hi @PaigeMiller

Thanks for your reply, and I tried yours in the very beginning, but result is same. Could you please let me know anything wrong with the following code? Thanks!
/* Check if A_&LOC_ID has any columns or not */
Proc Sql noprint;
   Select count(distinct name) into: does_column_exist
      from sashelp.vcolumn where lowcase(name) = 'noshow_cancel_pred_pos' and lowcase(memname) = 'a_&loc_id';
Quit;

If you have a macro variable, you can't put it inside single quotes. You must put it inside double quotes.

Re: How to determine if there is no column within a dataset

Crubal — Mon, 31 Jul 2017 13:53:19 GMT

Hi @PaigeMiller

Is it like the following? I tried but returns same result.

		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;

		%if &does_column_exist > 0 %then %do;

Thank you!

Re: How to determine if there is no column within a dataset

PaigeMiller — Mon, 31 Jul 2017 13:58:48 GMT

So let's stop right here and discuss some basic necessities of forum usage.

You can't say things like "it didn't work", or in your case "but returns same result". We have no idea what you mean. We don't know why it doesn't work or returns the same result.

You have to tell us what didn't work, and the best way to do that is to show us the relevant parts of your SASLOG. You also have to show us your SAS code (which you did), and in some cases you have to show us your data.

Re: How to determine if there is no column within a dataset

Crubal — Mon, 31 Jul 2017 14:32:37 GMT

Thanks @PaigeMiller

Code is like following:

%macro noshow_fcst;
OPTIONS NOTES SOURCE SOURCE2 MPRINT MLOGIC MERROR SYMBOLGEN;

data _null_;
 set PO_LOC end=last;
 if last then call symput('nfiles',_n_);
run;

%do i=1 %to &nfiles;
	%put i =;

	data _null_;
		set PO_LOC;

		if &i=_n_ then
			call symput('LOC_ID',LOC_ID);
	run;

	%Let Lead_Time = 0;
	
                /* HIST5_&LOC_ID IS obtained from Teradata database */

		/* Transfer pure Noshow and Cancellation from 'arrival date' & 'length of stay' level to 'stay date' */
		DATA HIST5_1_&LOC_ID;
			SET HIST5_&LOC_ID;
				Cancellation_Lead = Arrival_Date - Cancel_Date; 
					if Cancellation_Lead > 30 then Cancellation_Lead = 30;
					If Cancellation_Lead < 0 then Cancellation_Lead = 0;
				FORMAT Stay_Date yymmdd10.;
			do k=1 to length_of_stay;
				Stay_Date = Arrival_Date - 1 + k;
			output;
			end;
			drop k length_of_stay;
		run;

				/* Filter the data as we count 'No-show' for 1 night and Cancellation for Lead Time N */
		Data HIST5_2_&LOC_ID;
			Set HIST5_1_&LOC_ID;
			If Cancellations > 0 and Cancellation_Lead > &Lead_Time then delete;
			If Stay_Date > Arrival_Date and Noshows > 0 then delete;
		Run;
	

		/* Actual Noshow and Cancellation data by Stay Date (Month and Day of Week) level */
		Proc Sql;
			Create Table HIST5_3_&LOC_ID as select distinct
				Operating_Div_Cd,
				Chain_Cd,
				Loc_Id,
				Stay_Date,
				Month(Stay_Date) as Mon,
				Weekday(Stay_Date) as Dow,
				Prod_Class_Id,
				sum(Cancellations+Noshows) as Cancel_Noshow
			from HIST5_2_&LOC_ID
			group by 1, 2, 3, 4, 5, 6, 7
			order by 1, 2, 3, 7, 4;
		Quit;

		/* Forecasting Approach: Poisson Regression, omitting impact of Special Event */
		/* This part could obatin data for Noshow + Cancellation on some Lead Time */
		proc genmod data=HIST5_3_&LOC_ID;
			class Mon Dow;
			by Operating_Div_cd chain_cd loc_id prod_class_id;
			model Cancel_Noshow = Mon Dow/ dist=poisson
			link=log scale=pearson;
			output out=HIST_5_6_Pos_&LOC_ID p=Noshow_Cancel_Pred_Pos;
			where Stay_Date between &START1 and &END1;
		run;
		ods exclude all; 

		/* Check if HIST_5_6_Pos_&LOC_ID has any columns or not */
		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;

		%if &does_column_exist > 0 %then %do;

		/* Gather output data by Month and day of week level for Noshow + Cancellation */
		Proc Sql;
			Create Table Noshowfcst_&LOC_ID as select distinct 
				Operating_Div_Cd,
				Chain_Cd,
				Loc_Id,
				Prod_Class_Id,
				Mon,
				Dow,
				Case 
					When Weekday(Stay_Date) in (1,2,3,4,5) then 'WD'
					Else 'WE'
				End	as DY_TYP,
				avg(Noshow_Cancel_Pred_Pos) as Noshow_Fcst
			from HIST_5_6_Pos_&LOC_ID
			group by 1, 2, 3, 4, 5, 6, 7;
		Quit;
		
		%end;
			%else %do;
			%end;


				
%end;

%mend;
	/* The first macro ends for No-show Forecasting */

%noshow_fcst;

Part of Log Information for HIST_5_6_POS_&LOC_ID:

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

SYMBOLGEN: Macro variable LOC_ID resolves to AADAL

MPRINT(NOSHOW_FCST): 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_AADAL";
MPRINT(NOSHOW_FCST): Quit;

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.

Part of the data for 'hist_5_6_pos_&loc_id' :

Thank you! And for this example, 'Noshowfcst_&LOC_ID' cannot be obtained even if 'hist_5_6_pos_&LOC_ID' exists.

Re: How to determine if there is no column within a dataset

PaigeMiller — Mon, 31 Jul 2017 15:01:09 GMT

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.

Re: How to determine if there is no column within a dataset

Crubal — Mon, 31 Jul 2017 15:28:49 GMT

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':

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!

Re: How to determine if there is no column within a dataset

PaigeMiller — Mon, 31 Jul 2017 15:43:56 GMT

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.