Proc SQL into (MACRO)

[Ronein]

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;

[Ksharp]

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

[ballardw]

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.

[Tom]

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.