topic Re: Macro Var with null value in SAS Programming

Macro Var with null value

Ronein — Wed, 02 Feb 2022 11:43:53 GMT

Hello

User run this code 2 times:

First time with %let make=Audi

Second time with %let make=Tesla

I have some questions:

1-When user run make='Audi' then V2='Sedan','Sports','Wagon' and then I expect to get rows in dta set wanted.

Why do I get a data set with no rows?

2-when user run make='Tesla' then V2 will have null value and then I want to stop the process .

What is the way to do it please?


%let make='Audi';
/*%let make='Tesla';*/

Data ttt;
Input ID Type $ price $;
cards;
1 Audi Sedan 100000
2 Audi Sports 200000
3 Tesla Sports 150000
;
Run;

proc sql noprint;
select distinct compress(quote(type, "'")) into :V2  separated by ","
from sashelp.cars
where make=&make.
;
quit;
%put &v2;

IF %v2.  ne '' then do;
proc sql;
create table wanted as
select *
from  ttt
where  compress(Type) in (&v2.)
;
quit;
else Do nothing

Re: Macro Var with null value

PaigeMiller — Wed, 02 Feb 2022 12:11:04 GMT

When you run the first PROC SQL, you are accessing a data set that does not contain any Tesla (which is what SAS is telling you). With that hint, you should be able to figure it out yourself.

Debugging tip: when you get unexpected results, LOOK AT the data set being used with your own eyes.

Please do not send us code that you haven't debugged for OBVIOUS errors.

IF %v2.  ne '' then do;

will never work.

Please do not send us code that you haven't debugged for OBVIOUS errors.

quit;
else Do nothing

isn't even SAS code.

Re: Macro Var with null value

Kurt_Bremser — Wed, 02 Feb 2022 12:00:11 GMT

Maxim 2: Read the Log (and do it with diligence!)

 69         %let make='Tesla';
 70         
 71         proc sql noprint;
 72         select distinct compress(quote(type, "'")) into :V2  separated by ","
 73         from sashelp.cars
 74         where make=&make.
 75         ;
 NOTE: No rows were selected.

When "No rows were selected." happens, SQL does not (re)create the macro variable.

You need to create a "default" value first, and react on that value:

%let make=Tesla;

data ttt;
Input ID Type $ price $;
cards;
1 Audi Sedan 100000
2 Audi Sports 200000
3 Tesla Sports 150000
;

%let v2 =;

proc sql noprint;
select distinct compress(quote(type, "'")) into :V2  separated by ","
from sashelp.cars
where make="&make."
;
quit;

%IF &v2. ne %then %do;
proc sql;
create table wanted as
select *
from ttt
where compress(Type) in (&v2.)
;
quit;
%end;
%else %do;
%put No cars for this manufacturer!;
%end;

Re: Macro Var with null value

Sajid01 — Wed, 02 Feb 2022 13:52:29 GMT

Hello @Ronein
There are two issues with your code.
First you have enclosed Tesla in single quotes. So the values of make will be 'Tesla' and not Tesla.
Second in the Proc sql you have used where make=&make. As values of macro variables are of the type character, you must enclose in double quotes. Thus it should be where make="&make" . The code given by

@Kurt_Bremseris the proper way to do it.

Macro var with null value

Ronein — Wed, 02 Feb 2022 14:05:33 GMT

Hello

The macro variable V1 should contain nothing (null) because there is no Tesla cars in sashelp.cars

When I run this code I get warning

35 %put &V1;
WARNING: Apparent symbolic reference V1 not resolved.
&V1

What is the way to solve it and get null value into this macro variable?

%let make='Tesla';
proc sql noprint;
select distinct compress(quote(upcase(type), "'")) into :V1  separated by ","
from sashelp.cars
where make=&make.
;
quit;
%put &V1;

Re: Macro var with null value

PaigeMiller — Wed, 02 Feb 2022 14:11:48 GMT

Try assigning a null value first. Then, if there are no matching records found by PROC SQL, the macro variable still has a value. (Otherwise, as you have seen, the macro variable doesn't exist)

%let v1=;
%let make=Tesla;
proc sql noprint;
select distinct compress(quote(upcase(type), "'")) into :V1  separated by ","
from sashelp.cars
where make="&make."
;
quit;

Re: Macro Var with null value

Ronein — Wed, 02 Feb 2022 14:20:21 GMT

Great,

%let make=Audi;
/*%let make=Tesla;*/

%put &make; 
data ttt;
Input ID make $ Type $  price;
cards;
1 Audi Sedan 100000
2 Audi Sports 200000
3 Tesla Sports 150000
;
quit;


/*You need to create a "default" value first*/
%let v2 =;
proc sql noprint;
select distinct compress(quote(type, "'")) into :V2  separated by ","
from sashelp.cars
where make="&make."
;
quit;
%put &v2; 

%Macro RRR;
%IF &v2. ne %then %do;
proc sql;
create table wanted as
select *
from ttt
where compress(Type) in (&v2.)
;
quit;
%end;
%else %do;
%put No cars for this manufacturer!;
%end;
%mend RRR;
%RRR;

Re: Macro Var with null value

Tom — Wed, 02 Feb 2022 15:09:58 GMT

When you have a WHERE clause in the query PROC SQL might not ever write anything into the target macro variable.

In general it is easiest to set a default value before the query.

Or you can test the automatic macro variable SQLOBS to see if anything was found.

You also should remove the COMPRES() function calls. Is your data that messy? To make the generated macro variable shorter use TRIM() function instead to remove any trailing spaces that are stored in the fixed length character variables in the datasets.

data ttt;
  input ID Make $ Type $ price $;
cards;
1 Audi  Sedan 100000
2 Audi  Sports 200000
3 Tesla Sports 150000
;

%let make='Tesla';
proc sql noprint;
%let v2=;
select distinct quote(trim(type), "'") into :V2  separated by ','
  from sashelp.cars
  where make=&make.
;
%put &=sqlobs &=v2;
%if (&sqlobs) %then %do ;
create table wanted as
  select *
  from  ttt
  where  Type in (&v2.)
;
%end;
%else %do;
%put No types found for &=make. ;
%end;
quit;