topic Re: Why SQL "select into" needs separated by ' ' in SAS Programming

Why SQL "select into" needs separated by ' '

hhchenfx — Sat, 30 Mar 2019 02:40:12 GMT

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;

Re: Why SQL "select into" needs separated by ' '

Shmuel — Sat, 30 Mar 2019 02:51:25 GMT

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.

Re: Why SQL "select into" needs separated by ' '

Patrick — Sat, 30 Mar 2019 02:59:36 GMT

@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()

Re: Why SQL "select into" needs separated by ' '

hhchenfx — Sat, 30 Mar 2019 03:13:17 GMT

Thanks for your explanation.

It makes sense now.

HHC

Re: Why SQL "select into" needs separated by ' '

Patrick — Sat, 30 Mar 2019 03:24:03 GMT

@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

Re: Why SQL "select into" needs separated by ' '

Shmuel — Sat, 30 Mar 2019 05:48:18 GMT

@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.