Why SQL "select into" needs separated by ' '

[hhchenfx]

Hi,

I try to understand the logic.

If without separated by, why SAS only return first value, at least it can return all values without space

Thank you,

HHCFX

data have;
input abIc ID1 bxI4D b_ID2 xyzID_3; 
cards;
1 2 3 4 5
;run;

proc contents data=have out=varname;run;

proc sql;
select name into :droplist separated by ' ' from varname  where index(name,'ID')>0; quit;
%put &droplist;

*VS without separated by ' ';

proc sql;
select name into :droplist /*separated by ' '*/ from varname  where index(name,'ID')>0; quit;
%put &droplist;

[Shmuel · Accepted solution]

The "separated by ..." tells sql that you wants a list of names.

The list can be separated by any character given in a single quotes.

Otherwise the names are overwritten and you probably get the last one only.

[hhchenfx]

Thanks for your explanation.

It makes sense now.

HHC

[Patrick]

---

@Shmuel wrote:

The "separated by ..." tells sql that you wants a list of names.

The list can be separated by any character given in a single quotes.

Otherwise the names are overwritten and you probably get the last one only.

---

You get the value of the first row which is the smart thing as this way SAS doesn't have to process the whole table.

I've just tested for two tables - one with only a few rows and one with 20M rows. SQL ... INTO :name run in no time for both tables

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[Shmuel]

@Patrick , to your note

You get the value of the first row which is the smart thing as this way SAS doesn't have to process the whole table.

I've just tested for two tables - one with only a few rows and one with 20M rows. SQL ... INTO :name run in no time for both tables

selecting NAME by SQL - selects variable names from dictionary, no matter how many rows are in the dataset. 

[Patrick]

@hhchenfx 

That's just how it's implemented and the syntax works.

Alternatively to a list of values you can also create individual macro variables per row - so you've got all the options at your fingertips.

proc sql noprint;
  select name into :namelist separated by ''
  from sashelp.class
  ;
quit;
%put &=namelist;

proc sql noprint;
  select name into :m_name_1 - :m_name_9999
  from sashelp.class
  ;
quit;

%macro printMvars();
  %do i=1 %to &sqlobs;
    %put m_name_&i= &&m_name_&i;
  %end;
%mend;
%printMvars()