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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

9 REPLIES 9
FredrikE
Rhodochrosite | Level 12

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

 

%global TransposedColumnsF;

spastrana
Fluorite | Level 6

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

ballardw
Super User

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.

 

 

FredrikE
Rhodochrosite | Level 12

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

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

🙂

ChrisBrooks
Ammonite | Level 13

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

Tom
Super User Tom
Super User

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

spastrana
Fluorite | Level 6

thank you, that works!

Tom
Super User Tom
Super User

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
;

 

spastrana
Fluorite | Level 6

Thank you Tom!

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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