CODEWITHSUNDEEP
sassas-macro
Azeem112
Azeem112

Reputation: 377

how to enter empty value in SAS into variable if no records were found

I'm working on SAS and I'm getting values from data-sets and saving them in SAS into variables.

Sample data:

table
RK | ID | column_1 | column_2
1  | one| value_1  |  
2  | two| value_1  |  value_2

proc sql noprint;

select column_1 
into: variable_1
from table
where RK = 1;

select column_2
into: variable_2
from table
where RK = 1;

quit;

Now I want to use those variables in my report and if there is no data in my into variables I want to print a blank space. as

%put &variable_1;
%put &variable_2;

Result

value_1
&variable_2

if there is no value in my into variable I want it to print nothing but a blank space to my log or in my report.

How can I do this?

Expected result

value_1
(A blank space)

Upvotes: 1

Views: 4403

Answers (5)

Richard
Richard

Reputation: 27508

LOG

Named macro value logging is a shortcut syntax

%put &=variable_1; /* is almost the same as */
%put variable_1=&variable_1;

If the variable_1 contains unquoted semi-colons, or other confounding programmatic segments, it is better to log using superq. Macro variables can also be shown more clearly in the log by bracketing the value resolution. This will let you see leading and trailing spaces.

%put NOTE: variable_1=[%superq(variable_1)];

INTO

You can select specify more than one variable in the INTO clause

select a, b
into :a, :b

Basic INTO form does not trim values, and the target (macro variable) value length is based on source variable, computation length, or length as specified by the select items length= option.

select a length=50, substr(b,1,2)
into :a_50, :b_2

/* length of macro variable 'variable_1' will be the same length as column_1,
   regardless of the " ".  If the string literal was longer than column_1,
   the computation length is the string literals length.
*/
select case when column_1 is null then " " else column_1 end 
into: variable_1

There is additional syntax and keywords for INTO targets

  • trimmed value into a macro variable
    select A INTO :A trimmed
  • trimmed value from multiple rows into multiple (range of) macro variables
    select A INTO :A1-A99 /* populates range of &SQLOBS macro variables if <99 */
  • trimmed value from multiple rows into single macro variable
    select A INTO :A_csv separated by ','

NOTE: A trimmed blank value transferred to macro becomes a zero-length string. NOTE: A character null in SAS data set is a blank value, so you don't necessarily need a CASE or coalesce

The blank situation for variable_1 could be

select column_1 into: variable_1
select column_1 into: variable_1 trimmed
--- LOG ---
NOTE: variable_1=[            ];
NOTE: variable_1=[];

I speculate that the length of the macro variable value is determined during the SQL statement compilation/planning time, and can not be changed during execution time (meaning the target length won't change according to values found)

Case 1 - no rows selected

When the where selects no rows, there will be no cause for the INTO clause to operate, and thus no macro variables will be created. If the macro variables already existed before the query, the values will remain unchanged. Thus you should initialize each macro variable listed in the INTO clause prior to the query (per Azeem112).

%let variable_1=;
%let variable_2=;

proc sql noprint;

If your really need a single space, instead of nothing at all, initialize thusly

%let variable_1=%str( );
%let variable_2=%str( );

proc sql noprint;

Case 2 - rows selected

The value from the selected item is moved into a macro variable. The macro value has the same untrimmed length of the item, or if trimmed, the length of the item, or 0 if the item is a blank value. If you need a single space in the macro variable for the blank value case you could do

select column_1 into: variable_1 trimmed
...;
%let variable_1 = %qsysfunc(ifc(%length(%superq(variable_1)),%superq(variable_1),%str( )));

Upvotes: 0

Tom
Tom

Reputation: 51591

If the select statement does not return any rows (empty source table or no rows match where condition) then the macro variable(s) named in the into clause are not created. Just use a %let statement to set the default value before running the select statement.

proc sql noprint ;
%let infant_list=;
select name
  into :infant_list separated by ' '
  from sashelp.class 
  where age < 5 
;
quit;
%put &=infant_list;

If you really want a macro variable to contain a single space instead of nothing then you will need to use macro quoting.

%let infant_list=%str( );

Upvotes: 2

Azeem112
Azeem112

Reputation: 377

%let variable_1=;
%let variable_2=;

proc sql noprint;

select column_1 
into: variable_1
from table
where RK = 1;

select column_2
into: variable_2
from table
where RK = 1;

quit;

Upvotes: 2

user667489
user667489

Reputation: 9569

Using coalescec:

proc sql noprint;

select coalesecec(column_1," ") 
into: variable_1
from table
where RK = 1;

select coalesecec(column_2," ") 
into: variable_2
from table
where RK = 1;

quit;

Upvotes: 1

G.Arima
G.Arima

Reputation: 1171

Try this out:

proc sql noprint;

select case when column_1 is null then " " else column_1 end 
into: variable_1
from table
where RK = 1;

select case when column_2 is null then " " else column_2 end 
into: variable_2
from table
where RK = 1;

quit;


%put &variable_1;
%put &variable_2;

Upvotes: 0

Related Questions