topic Proc select into: how to save blank value in macro variable in SAS Programming

Proc select into: how to save blank value in macro variable

tinaz5012 — Fri, 04 Jun 2021 16:25:23 GMT

I am using the following code to read all values in column ccv_date_variabel into macro variables var1 - varn .

proc sql noprint;
   select strip(CCV_date_variable) into : var1 - 
   from &SPEC;
quit;

The problem is there are some values are blank, what I want is var1 has value blank and var2 has value "AESTDAT_RAW", but what I got is var1 equal to "AESTDAT_RAW". Could anyone please advise how can I solve this? thank you

Re: Proc select into: how to save blank value in macro variable

ballardw — Fri, 04 Jun 2021 16:32:25 GMT

You might provide just exactly how you intend to use that macro variable &var1.

How it is used would affect what the content should look like.

Re: Proc select into: how to save blank value in macro variable

Tom — Fri, 04 Jun 2021 16:34:16 GMT

I don't understand what you want that is different than what PROC SQL already generates.

data have;
  input x $10.;
cards;
 
ab
cd
 
ef
;
proc sql noprint;
 select x into :x1-  from have;
quit;

Results:

2405  %put &=sqlobs &=x1 &=x3 &=x5 ;
SQLOBS=5 X1= X3=cd X5=ef
2406  %put x1=%length(&x1) x2=%length(&x2);
x1=0 x2=2

Re: Proc select into: how to save blank value in macro variable

sbxkoenk — Fri, 04 Jun 2021 16:39:50 GMT

Hello,

I agree with @Tom , but you can also do it this way:

data have;
length abc $ 10;
abc='';       output;
abc='azerty'; output;
abc='querty'; output;
run;
data _NULL;
 set have;
 call symput('var'!!strip(put(_N_,5.)),abc);
run;
%PUT &=var1; %PUT &=var2; %PUT &=var3;

Koen

Re: Proc select into: how to save blank value in macro variable

Tom — Fri, 04 Jun 2021 17:04:36 GMT

CALL SYMPUT() will allow you to include leading/trailing spaces in the generated macro variable.

So if you want the empty values generate a macro variable with exactly one space in it you need to use CALL SYMPUT() and also use TRIM(LEFT()) instead of STRIP() to preserve that single space.

data _null_;
  set &spec ;
  call symput(cats('var',_n_),trim(left(CCV_date_variable)));
run;

Re: Proc select into: how to save blank value in macro variable

tinaz5012 — Fri, 04 Jun 2021 17:54:17 GMT

Thank you for your help, appreciate it!