BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Crubal
Quartz | Level 8

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
Diamond | Level 26

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.

--
Paige Miller
Crubal
Quartz | Level 8

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;

 

Question1.PNG

 

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
Diamond | Level 26

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.

--
Paige Miller
Crubal
Quartz | Level 8

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
Diamond | Level 26

You need to use double quotes in your PROC SQL.

--
Paige Miller
Crubal
Quartz | Level 8

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.

Question1.PNG

PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Crubal
Quartz | Level 8

Finally solved this time, really appreciate your help @PaigeMiller

PaigeMiller
Diamond | Level 26

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

 

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 26 replies
  • 4353 views
  • 1 like
  • 3 in conversation