topic Re: Apparent symbolic reference not resolved in SAS Programming

Missing functions inside a proc sql case statement is failing

spastrana — Tue, 26 Sep 2017 15:55:02 GMT

I have a variable that is missing because are no rows that meet the criteria. I need to put a check in place so if the variable is missing, my code does not fail. here is what I have:

proc sql noprint;

select name

into :TransposedColumnsF

separated by ','

from dictionary.columns

where libname='EGTASK'

and memname ='TRNSTRANSPOSEDFSERIES_MEMBER'

AND NAME LIKE 'Column%';

quit;

/* transposedColumnF should not return anything*/

/* then I have a case statement to that my query does not fail when the variable does not have any values*/

proc sql;

CREATE TABLE EGTASK.MyNewTable AS

SELECT

case when missing("&TransposedColumnsF.") then ''

else (CATX(",",&TransposedColumnsF))LABEL="Disrupted Drugs" end AS 'Disrupted Drugs'n

FROM EGTASK.SomeOtherTable t1;

quit;

/* I get the following error: "ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string....." The code does not fail if &TransposedColumnsF actually gets a value*/

Re: Apparent symbolic reference not resolved

FredrikE — Tue, 26 Sep 2017 15:53:59 GMT

I think you just need to make it global before your sql 🙂

%global TransposedColumnsF;

Re: Apparent symbolic reference not resolved

spastrana — Tue, 26 Sep 2017 15:58:30 GMT

Hi Federik, thank you for your reply. it still fails

Re: Missing functions inside a proc sql case statement is failing

ballardw — Tue, 26 Sep 2017 16:00:06 GMT

What value would you want the variable &TransposedColumnsF to have when it is missing in the SQL part?

Use a %let statement before the SQL to assign that value

%let TransposedColumnsF = Text when missing;

proc sql noprint;

...

quit;

If the SQL is successful it will overwrite the value into the macro variable. If not the preset remains.

Re: Missing functions inside a proc sql case statement is failing

FredrikE — Tue, 26 Sep 2017 16:01:57 GMT

Actually it looks like you have forgotten some quotation....:

CATX(",",&TransposedColumnsF)) should be CATX(",","&TransposedColumnsF"))

🙂

Re: Apparent symbolic reference not resolved

Tom — Tue, 26 Sep 2017 16:09:51 GMT

Try just adding a %LET statement before your SELECT statement so that you insure the macro variable is created even when there are no matches.

proc sql noprint;
%let TransposedColumnsF=;
select name
  into :TransposedColumnsF separated by ','
  from dictionary.columns
  where libname='EGTASK'
    and memname ='TRNSTRANSPOSEDFSERIES_MEMBER'
    AND NAME LIKE 'Column%'
;
quit;

You also need to make sure that you are generating proper syntax for the CATX() command when there are no variables selected.

If you are not in a macro (and so cannot use %IF statement) you could use the IFC() funtion to conditionally generate some code.

So I think this is what you intended your CASE statement to do but there was no need for a CASE statement since it was not based on anything that changed per observation in the table.

CREATE TABLE EGTASK.MyNewTable AS
  SELECT %sysfunc(ifc(&sqlobs>0,CATX(",",&TransposedColumnsF),' '))
    AS 'Disrupted Drugs'n LENGTH=200 LABEL="Disrupted Drugs" 
  FROM EGTASK.SomeOtherTable t1
;

Re: Missing functions inside a proc sql case statement is failing

spastrana — Tue, 26 Sep 2017 16:15:30 GMT

thank you, that works!

Re: Apparent symbolic reference not resolved

spastrana — Tue, 26 Sep 2017 16:17:01 GMT

Thank you Tom!

Re: Missing functions inside a proc sql case statement is failing

ChrisBrooks — Tue, 26 Sep 2017 16:20:08 GMT

When you run a Proc SQL select into SAS will not create a macro variable if does not find any matching rows e.g.

63         proc sql;
 64         select name into :mynames
 65         separated by ','
 66         from sashelp.class
 67         where name="Colin";
 NOTE: No rows were selected.
 68         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.01 seconds
       cpu time            0.02 seconds
       
 
 69         
 70         %put &mynames;
 WARNING: Apparent symbolic reference MYNAMES not resolved.

This means that the macro variable will not resolve to missing because it doesn't exist. You can use the suggestion from @FredrikE to create a default value beforehand which may be the best way to handle this scenario. Interestingly I believe the bahaviour of Proc SQL in this regard changed in SAS v 9.2 or 9.3 - before then SAS would create an empty macro variable which is why you may see something like this work in old code on the web...

Re: Missing functions inside a proc sql case statement is failing

Tom — Tue, 26 Sep 2017 16:29:31 GMT

@ChrisBrooks wrote:

Interestingly I believe the bahaviour of Proc SQL in this regard changed in SAS v 9.2 or 9.3 - before then SAS would create an empty macro variable which is why you may see something like this work in old code on the web...

Nope. It has always worked this way. There are other ways than using a %LET to insure that the macro variable exists. You could use a %GLOBAL or %LOCAL statement to define the macro variable. Note that when using %LOCAL statement insdie of macro it works almost like using %let to initialize to empty since by default a new macro variable has no content and local macro variables are not remembered across mutliple calls. But a %GLOBAL statment will not re-initalize a macro variable that already exists and a global macro vairable will keep its value until reset or deleted.