Apparent symbolic reference not resolved.

[kencyr]

Hello world.

I am a new SAS programmer and I've been working on a macro do work with the dictionary tables and, as the title indicates, I am getting an error.

the setup is a simple 10 record test table in the Ruser library

Proc sql;

create table Ruser.Test as

     select

               *

     from

          dictionary.columns

     where

          name = "<insert memname here>" /*pick a table of your choice and insert it here*/

     ;

     quit;

here is the macro:

%macro tester(lib);

%local colcount;

%local collen;

/* The first SQL procedure obtains the count for the second SQL procedure */

proc sql noprint;

select count(*) into :count

from dictionary.tables

where upcase(libname)=%upcase("&lib");

quit;

%do i=1 %to &count while i>= &count;

/* The second SQL procedure obtains the memname variable */

      proc sql noprint;

      select upcase(memname) into :dsname1 - :dsname%TRIM(%LEFT(&count))

      from dictionary.tables

      where upcase(libname)=%upcase("&lib");

quit;

%local tablecount ;

%let tablecount= %TRIM(%LEFT(&count));

%end;

/* Section below  gets the count of columns in each dataset*/

%do k=1 %to &tablecount;

      proc sql noprint;

      select count(*) into :countCol%TRIM(%LEFT(&count))

            From &lib..%upcase(&&dsname&k)

      ;

      quit;

/* here is where the error crops up */

      %let colcount= "&&countCol%TRIM(%LEFT(&count))";

      %let collen = %length(&&colcount);

      %put(" length is &collen");

%end;

%mend tester;

%tester(RUSER);

the log tells me:

WARNING: Apparent symbolic reference COUNTCOL not resolved.

WARNING: Apparent symbolic reference COUNTCOL not resolved.

(" length is 10")

WARNING: Apparent invocation of macro SYSMEXECDEPTH not resolved.

what I have tried:

as is: "      34" ( a text string, right value in it, but it ought to be numeric)

2&& no double quote: &countCol1

1& with double quote: "      34" (again, right value, but a string)

1&, no double quote: &countCol1

I am out of ideas, and my researches have taught me a lot, but not what is going on here..

anyone able to tell me what I am missing, or offer adivce?

Thanks

(note: edited for spelling)

[Reeza]

Doesn't the dictionary table have the number of columns in the table or the sashelp.vcolumns?

You don't need to count it with another proc sql. 

[kencyr]

Hrm. I did not know that. I will investigate that bit and try to use it.

but still, can you tell me why it is coming up unresolved?

Thanks for replying!

[data_null__]

%let count=&count;

[kencyr]

I'm sorry Data_Null, I get the statement, but ...

a) where would it go?, and

b) why would it make a difference? (probably the more important question)

many thanks!

[Tom]

You are jumping through hoops because the COUNT variable generated by PROC SQL had spaces in the front.  Such unquoted leading (or trailing) spaces can be easily remove with a simple %LET statement.  There are other ways to prevent SAS from generating the spaces in the first place.

For example this multi step process

/* The first SQL procedure obtains the count for the second SQL procedure */

proc sql noprint;

select count(*) into :count

from dictionary.tables

where upcase(libname)=%upcase("&lib");

quit;

%do i=1 %to &count while i>= &count;

/* The second SQL procedure obtains the memname variable */

      proc sql noprint;

      select upcase(memname) into :dsname1 - :dsname%TRIM(%LEFT(&count))

      from dictionary.tables

      where upcase(libname)=%upcase("&lib");

quit;

%local tablecount ;

%let tablecount= %TRIM(%LEFT(&count));

Can be simplified to

/* Get MEMNAMES into macro variables  */

proc sql noprint;

  select memname into :dsname1 - :dsname99999

      from dictionary.tables

      where libname =%upcase("&lib")

  ;

quit;

%local tablecount ;

%let tablecount= &sqlobs ;

Then you do not need all of those left(trim()) clutter when you want to reference the counts.

[kencyr]

Tom, some good guidance...I'll try it out!

[Vince28_Statcan]

Based on your desired objectives for your macro, you will definitely significantly lessen the syntax difficulties using the automatic variable &SQLOBS for your countsas well as the range-macro variables as Tom pointed out.

As an attempt to provide a clearer explanation why the unquote/nrstr combination solves the very issue that you had, I will suggest a few things.

1. Whenever errors or warnings are issued for "macro variable blah resolves or cannot be resolved", use option symbolgen; and read the details of the segment around your warnings/errors. It basically provides each step that the macro processor does to execute the macro elements of any given line (at compilation phase).

2. Always keep in mind that the core concept behind the macro processor is that of a text parser. It basically allows you to parse a bunch of SAS code that you would just not want to hard type. The difficulty underlying with this, is that anytime you need to extract data (through data step or proc sql in general) via execution phase statements to store it in macro variables for use within the same macro. It can create a giant turmoil of timeliness (the macro variables you thought had been created will only be "later" when the code segment gets executed so when you attempt to dereference them, they don't exist)

3. Macro variables dereferencing happens naturally before macro functions (This is the main issue that caused your error). Thus, if you ought to do tricky task where information about a macro variable that you wish to deref is derived after applying an inline %function, you will need to manipulate the macro processor to delay the outer ampersands until after the %functions are resolved.

Let's go back to the example by Jakkas

%Let colCount = &&countCol%Trim(%Left(&count));

In this scenario, since ampersands are resolved before the %functions, the macro processor does this (which can be validated with options symbolgen):

1st pass

&& resolves to &, &count resolves to            5

2nd pass

&countCol cannot be resolved, issueing the error

3rd pass (inner functions first)

%left(          5) is resolved to 5 (symbolgen will use TEXT automatic macro var for most text-oriented in-line macro functions)

4th pass (outer function)

%trim(5) is resolved to 5

However, %nrstr() prevents any macro elements inside the paranthesis to be resolved by the macro processor unless controlled otherwise.

Thus %Let colCount = %unquote(%nrstr(&countCol)%Trim(%Left(&count.))); is resolved as follow

first pass

skip over &countCol because it is %nrstr(), resolve &count as          5

2nd pass (resolving %inline function from inside to outside)

%left(          5) resolves as 5

3rd pass

%trim(5) resolves as 5

4th pass

%unquote(%nrstr(&countCol)5) resolves as &countCol5

5th pass

&countCol5 resolves as 10000

in practice %nrstr(&countCol) actually does get resolved and if you read the documentation, SAS simply changes the & representation to it's unicode?(maybe it's ascii or hex or even binary representation - regardless, not one that is read as an & by the macro processor). For simplicity, simply consider that it only gets resolved once it is unquoted by %unquote.

Hope this explanation helps you a little.

P.S. %STR, %NRSTR, %QUOTE, %NRQUOTE and %UNQUOTE eventually are necessary but very, very often. The code can be simplified elsewhere to avoid having to twist your bring to figure out when what should be resolved and have a better code flow/readability. Hence, the tips pointed above, namely the generic one to remove leading blanks and the various proc sql interractions with the macro facility pointed out by Tom are much better alternatives.

[Jakkas]

If I understand this correctly then the issue you are having is the same as the issue with the following code

data temp;

     Do i =1 To 5; output; end;

Run;

Proc Sql;

     Select Count(*) into :Count from temp;

Quit;

%Let countcol5 = 10000;

%Let colCount = &&countCol%Trim(%Left(&count));

%Put &colcount;

and this can be fixed as

data temp;

     Do i =1 To 5; output; end;

Run;

Proc Sql;

     Select Count(*) into :Count from temp;

Quit;

%Let count = &Count;     /* one way to get rid of blanks. */

%Let countcol5 = 10000;

%Let colCount = &&countCol&count;

%Put &colcount;

[kencyr]

Jakkas, I suspect your post was cut short?

All, MANY thanks for the replies

[Vince28_Statcan]

Going from Jakkas' example, the issue is that %trim and %left functions are causing issue because they are normally resolved after the ampersand dereferencing (which only makes sense since you ought to have the inner macro variables derefed before the functions are applied)

As such, here is an alternative to fix the dereferencing order

options symbolgen;
data temp;
     Do i =1 To 5; output; end;
Run;
Proc Sql;
     Select Count(*) into :Count from temp;
Quit;
%Let countcol5 = 10000;
%Let colCount = %unquote(%nrstr(&countCol)%Trim(%Left(&count.)));
%Put &colcount;

Since % macro functions resolving goes from inside out, it goes

&count.

%left

%trim

(the %nrstr(&countcol) prevents the & to be read as a macro syntax element but rather just as raw text just yet)

%unquote allows to deref the &countcol that was quoted after the %trim/%left have already been resolved

[Vince28_Statcan]

There is a lot of things that I am questionning myself about in your syntax logic making it difficult to seize exactly where the issue is.

My first suggestion is to change use an intermediate step to do the trim/left combo on count only once so that all further deref of &count. needs not to be formated.

Based on the Error log, it appears as though %TRIM(%LEFT()) does not get resolved within the double quotes of statement

%let colcount= "&&countCol%TRIM(%LEFT(&count))";

It appears like a str/quote/unquote job has to be worked around. However, I don't quite understand

I suggest you provide your desired objectives for this macro so that we can help you polish the idea a little. The count of column in each dataset segment I can't understand what is your desired intention with the 3 statements preceeding the %end;

Also, interractions between macros and raw code (in your case proc SQL; ) for each raw code segment that produces macro variable can be very tricky.

Since macro statements are normally ran at compilation phase whereas raw SAS code statements are only ran at execution phase, it is quite possible that, because they are embeded within a %do ...; %end; macro loop block, that each

      proc sql noprint;

      select count(*) into :countCol%TRIM(%LEFT(&count))

            From &lib..%upcase(&&dsname&k)

      ;

      quit;

only get executed after the entire %do/%end block are executed. Thus, at compilation phase of this segment of the macro, :countCol&tablecount. has not been assigned a value because the proc sql was not yet ran (it only is after the entire do/end block has been parsed as code for execution). That could be another reason why you get this bug.

As an example of a way around (and to test if it is that which is causing your bug) I suggest trying the following change

from

%do k=1 %to &tablecount;

      proc sql noprint;

      select count(*) into :countCol%TRIM(%LEFT(&count))

            From &lib..%upcase(&&dsname&k)

      ;

      quit;

/* here is where the error crops up */

      %let colcount= "&&countCol%TRIM(%LEFT(&count))";

      %let collen = %length(&&colcount);

      %put(" length is &collen");

%end;

to

%do k=1 %to &tablecount;

      proc sql noprint;

      select count(*) into :countCol&k.

            From &lib..%upcase(&&dsname&k)

      ;

      quit;

%end;

%do k=1 %to &tablecount;

      %let colcount= "&&countCol&k..";
     %put colcount=&colcount.;

      %let collen = %length(&&colcount);

      %put(" length is &collen");

%end;

This should help you give some insight on what was wrong in that segment

[kencyr]

Vince,

your code seems to have corrected the issue in a test copy (now I have to compare and see why...you have a good amount of information there)

to answer the question about the objectives:

the ultimate objective is for me to have a program that will, once supplied with a library parameter, generate a large chunk of table level data dictionary & research information (stuff like % populated, min, max, format,informat, continuous or discrete values and a few other things.).

The idea was to make use of SAS metadata and work it as follows: (apologies for poor formatting)

Step     Action

1      get count of tables in library                        

2      Loop 1 begins                  

3             put table names into variable                  

4      loop 1 ends                      

5      Loop 2 begins                    

6             put count of columns into variable             

7             Loop 3 begins             

8                    put column names into variable          

9                          loop 4

10                                get count of obs by column

11                                Pass count to a table

12                         Loop 4 ends  

13            Loop 3 ends               

14     Loop 2 ends  

here is the full macro in it's unbridled, horrible, noobness (and yes I know it is bad, I'm working on it!!!)

Notes:

• This macro uses some debugging code taken from Quentin McMullen:

            http://www.bi-notes.com/2012/10/sas-code-show-sas-macro-variables/?goback=%2Egde_85005_member_174589...

• There are MANY errors/bugs, particularly later in the macro, the reason I posted the question is to start learning what I did wrong, and fixing it
• I have not, in the copy below, incorporated the change reccomended by Vince
• Per Reeza's comment I need to investigate getting the column count from the metadata

The macro:

%macro Step1(lib,debug=1);

%local colcount;

/* The first SQL procedure obtains the count for the second SQL procedure */

proc sql noprint;

select count(*) into :count

from dictionary.tables

where upcase(libname)=%upcase("&lib");

quit;

%put("Step1 completed");

%do i=1 %to &count while i>= &count;

/* The second SQL procedure obtains the memname variable */

      proc sql noprint;

      select upcase(memname) into :dsname1 - :dsname%TRIM(%LEFT(&count))

      from dictionary.tables

      where upcase(libname)=%upcase("&lib");

quit;

%local tablecount ;

%let tablecount= %TRIM(%LEFT(&count));

%end;

%put("Step 2 completed");

/* Section below  gets the count of columns in each dataset*/

%do k=1 %to &tablecount;

      proc sql noprint;

      select count(*) into :countCol%TRIM(%LEFT(&count))

            From &lib..%upcase(&&dsname&k)

      ;

      quit;

%put("below")

/*unresolved reference here*/

      %let colcount= &&countCol%TRIM(%LEFT(&count));

      %*If in debug mode, dump local macro vars to log;

       %if &debug=1 %then %do;

              %printMacVars(scope = Step1)

      %end;

%put("above");

      %local Tbl;

      %let tbl = %upcase("&&dsname&k");

                  %do y=1 %to &colcount;

/*Cname is also unresolved but oddly still gets the values into the macro variables*/

                        proc sql feedback noprint;

                        select upcase(name) into :cname1 - :cname%trim(%left(&colcount))

                        from &lib..%upcase(&&dsname&k)

                        ;

                        quit;

                        proc sql feedback noprint;

                        select count("&cname&y") into :countresult1

                        from &lib..%upcase(&&dsname&k)

                        ;

                        quit;

                        %local ccol;

                        %let ccol =  %upcase("&&cname&y");

/* This point and up works*/

                        proc sql feedback;

                        update %upcase(&lib..test)

                              set counts = &countresult1

                        where

                              %upcase(&lib..name) = %upcase(&ccol)

                        ;

                        quit;

                  %end;

%end;

%put("step 3 ends here");

%mend Step1;

Message was edited by: Mike Montgomery copy/paste error

[yaswanthj]

Try this code... i did some manipulation in your code..please let me know, if you have any difficulties..

%macro tester(lib);

%local colcount;

%local collen;

/* The first SQL procedure obtains the count for the second SQL procedure */

proc sql noprint;

select count(*) into :count separated by ','

from dictionary.tables

where upcase(libname)=%upcase("&lib");

quit;

%do i=1 %to &count. while i>= &count.;

/* The second SQL procedure obtains the memname variable */

      proc sql noprint;

      select upcase(memname) into :dsname1 - :dsname%TRIM(%LEFT(&count.))

      from dictionary.tables

      where upcase(libname)=%upcase("&lib");

quit;

%local tablecount ;

%let tablecount= %TRIM(%LEFT(&count.));

%end;

/* Section below  gets the count of columns in each dataset*/

%do k=1 %to &tablecount;

      proc sql noprint;

      select count(*) into :countCol&count. separated by ','

               From &lib..%upcase(&&dsname&k)

      ;

      quit;

/* here is where the error crops up */

      %let colcount= %STR(&&countCol&count.);

      %let collen = %length(%str(&colcount));

      %put(" length is &collen");

%end;

%mend tester;

%tester(test);

[kencyr]

Good afternoon all,

I have taken a lot of the advice offered here and here is the final result (of this stage).

Macro code follows (apologies for formatting)

%macro Step0(lib,debug=1);

/* Get MEMNAMES into macro variables  */

proc sql noprint;

  select memname into :dsname1 - :dsname99999

      from dictionary.tables

      where libname =%upcase("&lib")

  ;

quit;

%local tablecount ;

%let tablecount= &sqlobs ;

%do i=1 %to &tablecount;

      proc sql feedback;

            select upcase(name) into :cname1 - :cname999999

            from &lib..%upcase(&&dsname&i)

            ;

            quit;

      %local Colcount ;

      %let Colcount= &sqlobs ;

      %do k=1 %to &colcount;

            proc sql feedback;

            select count(name) into :countresult1

            from &lib..%upcase(&&dsname&i)

            where

            %upcase(name) = %upcase("&&cname&k")

            ;

            quit;

            proc sql;

            Update Ruser.Test /*make this a parameter? */

            Set Counts = &countresult1

            where

            %upcase(name) = "&&cname&k"

            ;

            quit;

      %end;

      %*If in debug mode, dump local macro vars to log;

       %if &debug=1 %then %do;

              %printMacVars(scope = Step0)

      %end;

%end;

%Mend Step0;

%Step0(RUSER, debug = 1);

Lessons learned:

1) there a lot of useful bits in SAS I am not aware of (example:&sqlobs) I had the right ideas through this little project, but...not quite the right approach.

2) scope matters - it helps to make sure you are using the correct loop variable at all times (corollary: don't get to clever, you'll outwit yourself)

3)if it looks to complicated, you are likely doing it wrong.

I thank everyone for their assistance and explanations. I have a lot to chew on with all of the replies in this topic, and a lot to learn.

Thanks again!