topic Re: Proc SQL into (MACRO) in SAS Programming

Proc SQL into (MACRO)

Ronein — Wed, 12 Mar 2025 05:25:55 GMT

Hello

In data set ttt there are 135 rows (In each row there is a var name in field field_name)

I am using this code to create a macro var that concatenate the var names

I see in Log that the macro var has only concatenation of 75 values. WHY????

proc sql noprint;
select field_name into : Vfields  separated by ' '
from ttt
;
quit;
%put &Vfields;

Re: Proc SQL into (MACRO)

Ksharp — Wed, 12 Mar 2025 05:54:53 GMT

Maybe there are some missing value in "field_name ".
Check it by
proc freq data=ttt;
table field_name /missing;
run;

Re: Proc SQL into (MACRO)

ballardw — Thu, 13 Mar 2025 01:39:54 GMT

Or place an actual character between values. If you see multiples of the character with no readable text between the values then you have missing values for the variable.

Re: Proc SQL into (MACRO)

Tom — Thu, 13 Mar 2025 03:02:08 GMT

There are two things that could cause that.

The simplest is that some of the field_names are empty.

In this example the query processed 19 observations but on 10 names where written into the macro variable. Note; In SAS a MACRO is something completely different than a macro VARIABLE.

76   proc sql noprint;
77   select case when(sex='M') then name else ' ' end into :namelist separated by ' '
78   from sashelp.class
79   ;
80   %put &=sqlobs n_names=%sysfunc(countw(&namelist,%str( )));
SQLOBS=19 n_names=10
81   quit;

The other cause I have seen is queries against Teradata which defaults to returning only the unique set of observations that satisfy the query. So if the same field_name appeared 10 times in the dataset being queried only one copy would be returned.