DATA Step, Macro, Functions and more

Missing functions inside a proc sql case statement is failing

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Missing functions inside a proc sql case statement is failing

[ Edited ]

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*/

 

 


Accepted Solutions
Solution
‎09-26-2017 12:17 PM
Super User
Posts: 11,343

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

Posted in reply to spastrana

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.

 

 

View solution in original post


All Replies
Regular Contributor
Posts: 191

Re: Apparent symbolic reference not resolved

Posted in reply to spastrana

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

 

%global TransposedColumnsF;

New Contributor
Posts: 4

Re: Apparent symbolic reference not resolved

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

Solution
‎09-26-2017 12:17 PM
Super User
Posts: 11,343

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

Posted in reply to spastrana

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.

 

 

Regular Contributor
Posts: 191

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

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

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

Smiley Happy

Super Contributor
Posts: 440

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

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...

Super User
Super User
Posts: 7,050

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

Posted in reply to ChrisBrooks

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.

New Contributor
Posts: 4

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

thank you, that works!

Super User
Super User
Posts: 7,050

Re: Apparent symbolic reference not resolved

Posted in reply to spastrana

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
;

 

New Contributor
Posts: 4

Re: Apparent symbolic reference not resolved

Thank you Tom!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 168 views
  • 4 likes
  • 5 in conversation