Filtering user data / WARNING: Apparent invocation of macro VDB_DT not resolved

[cuevasj]

SAS Visual Data Builder, version 7.3 hotfix 04

I have a data table, lets call it DATA_TABLE. I want to take this table and add the following condition in the "Where" area:

 upcase(strip(DATA_TABLE.variable1))=upcase(strip("&SYSUSERID."))   

However, when I add this statement to the where clause, I get a prompt to enter default authorization credentials, so I enter my own. When I do, the resulting view will only display my credentialed data when I try to build a report in VA. How do I get Data Builder to not prompt me, so that the view will populate the table based on who is signed in at the time?

When I try to load the DATA_TABLE with no where clause and hit 'preview', I get the following error message

WARNING: Apparent invocation of macro VDB_DT not resolved

I see in the code where there is a call for %VDB_DT, but I don't see anywhere in the code displayed where this macro is defined. Is this a permissions issue?

See Attachment for pictures and log

PS

I tried to hit cancel and not enter my credentials, but then the view is unusable, because it returns zero rows.

I also tried to load the LASR version of the table and add the where clause there, but that was also unsuccessful.

[RW9]

I dont know abut your macro, but this:

upcase(strip('&SYSUSERID.'))

Does not do what you think.  To resolve macro variables they need to be between double quotes:

upcase(strip("&SYSUSERID."))

Otherwise your just comparing the text string '&SYSUSERID.'

[cuevasj]

Yeah, double quotes don't work either.

[RW9]

Also the other part of that code is also wrong:

 upcase(strip(DATA_TABLE.variable1))=upcase(strip("&SYSUSERID."))   

You do not reference variables by <dataset>.<variable>

upcase(strip(variable1))=upcase(strip("&SYSUSERID."))   

Also, post full code and log, its impossible to guess from "it doesn't work".

[cuevasj]

I appreciate your responses.
In SAS VA, Data Preparation, you do have to have the table_name.variable_name. In EG, you wouldn't.

[RW9]

It sounds like the system is generating SQL behind the scenes then (unless they have a completely new language for this app), and it automatically creates and Alias called the same as the datasetname, and what you use there is the alias, not the actual dataset name.  

As for trying to solve this, unless we see a log of what code is running, inputs/outputs, errors messages etc. its not going to be possible to debug.  The log is key information in what is actually being run with all resolutions to macros and such like being shown.

[cuevasj]

I attached the log to the original thread with screen shots

[RW9]

Ok, so there seem to be two parts to this the first:

WARNING: Apparent invocation of macro VDB_DT not resolved.
48         
49         /* Drop existing table */50         %vdb_dt(TEMP_0);
180 ERROR 180-322: Statement is not valid or it is used out of proper order.

There is nothing in that log to indicate that this macro is defined, maybe you missing a startup library, or someone else's include file, can't tell.

And then the second:

85 WHERE

86 strip(upcase(CP_SPARK.supervisor_id))=strip(upcase("&SYSUSERID.")) ;

There is not error on this or warning.  It is simply telling you that your where clause does not return any rows.  Perhaps do a print of the cp_spark dataset, just the supervisor_id, and in your program put:

%put &sysuserid.;

So you can actually see what value is being used, I can't do this for you, all I can say is that there are no rows where supervisor_id=&sysuserid.

To note, my assumption about aliases was true:

84 cpmart.CP_SPARK CP_SPARK

[Tom]

Sounds like your macro variable SYSUSERID contains a string like %VDB_DT that SAS is thinking is a call to macro that is not defined?

Otherwise there is some other code being generated that you haven't shown that is causing that error message.

If this is SAS code then perhaps you can add code to generate the value inside of single quotes instead of double quotes to avoid the macro processor looking at it?  So instead of 

"&sysuserid"

use 

%sysfunc(quote(%superq(sysuserid),%str(%')))

[cuevasj]

Thank you for your support.
SYSUSERID is not my macro, it is a built-in SAS macro variable for identifying who is logged in to SAS. The %VDB_DT is trying to drop a view in the VA code (attached to original post).

[Tom]

There was no code or log attached to the original post. Just a PDF file that the forum editor could not display for some reason. To include log in your post using the Insert Code icon (looks like {i} ) and paste the text into the pop-up window.

[Quentin]

So %VDB_DT is a macro provided by SAS, correct?  All of these different solutions from SAS handle macro definitions in different ways.  Sometimes you see the macro definition in the code.  Sometimes it's in an autocall library somewhere.  If it's in an autocall library, it could be a permissions issue if somehow you don't have read access to the directory.  There are more options as well.

I don't know SAS Visual Data Builder. If you can get to a place you can submit code, you could try debugging by seeing where the autocall macros are stored, then looking through that directory for a file vdb_dt.sas .   But that's just a guess.  

You could also just submit to tech support, since it seems like it's their auto-generated code that is breaking.

You might test to see if just adding an obviously true where clause, "WHERE 1;" would trigger this problem in a little simple example.  That way the resolved value of &sysuserID won't be a potential issue.

Also you could close and restart.  By default, if SAS will only look for the definition of an autocall macro once in your SAS session.  So if you had a permissions issue that is now resolved, you would need a new session in order to run %VDB_DT.

[Madelyn_SAS]

Visual Data Builder is using a specific workspace server. Look in Preferences for the name of the server (.e.g, SASApp). Then, on that physical machine, check that these files exist:

        ./SASHome/SASFoundation/9.4/sasautos/vdb_dt.sas

        ./SASHome/SASFoundation/9.4/sasautos/vdb_execute_query_in_parallel.sas