topic Re: WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause. in SAS Programming

WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

France — Wed, 13 Jun 2018 19:57:09 GMT

hello all,

I use the following codes to get the min date of a group of data which have same 'earliest_filing_id'

proc sql;

create table step1.earlist_grant_date_earlist_id as
SELECT 
Earlist_grant_date.earliest_filing_id, 
put(min(Earlist_grant_date.publn_date),YYMMDDd10.) into: Earlist_grant_date_final
from Step1.Earlist_grant_date as Earlist_grant_date
group by Earlist_grant_date.earliest_filing_id
order by Earlist_grant_date.earliest_filing_id
;
quit;

the result shows

111  proc sql;
112
113  create table step1.earlist_grant_date_earlist_id as
114  SELECT
115  Earlist_grant_date.earliest_filing_id,
116  put(min(Earlist_grant_date.publn_date),YYMMDDd10.) into: Earlist_grant_date_final
117  from Step1.Earlist_grant_date as Earlist_grant_date
118  group by Earlist_grant_date.earliest_filing_id
119  order by Earlist_grant_date.earliest_filing_id
120  ;
WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.
NOTE: Table STEP1.EARLIST_GRANT_DATE_EARLIST_ID created, with 32123844 rows and 2 columns.
121  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           3:44.20
      cpu time            30.18 seconds

what is the meaning of

WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.
NOTE: Table STEP1.EARLIST_GRANT_DATE_EARLIST_ID created, with 32123844 rows and 2 columns.

should I rewrite the code to avoid this warning ?

thanks in advance.

Re: WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

PaigeMiller — Wed, 13 Jun 2018 19:58:52 GMT

It means exactly what it says. You don't have the same number of variables before and after INTO. So the SQL using INTO doesn't make any logical sense here.

Re: WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

France — Wed, 13 Jun 2018 20:05:22 GMT

thanks for your reply @PaigeMiller

but what should I do for it ? could you please give me some suggestions ? I would like to have two columns, one is 'earliest_filing_id', the other is minimum of 'publn_date'.

thanks a lot

Re: WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

Reeza — Wed, 13 Jun 2018 20:19:13 GMT

into: is used to create macro variables not tables. In T-SQL I believe INTO was used to create a table. In SAS that's handled with the CREATE TABLE statement at the top of the code.

proc sql;

create table step1.earlist_grant_date_earlist_id as
SELECT 
Earlist_grant_date.earliest_filing_id, 
put(min(Earlist_grant_date.publn_date),YYMMDDd10.) as Earliest_grant_date_final
from Step1.Earlist_grant_date as Earlist_grant_date
group by Earlist_grant_date.earliest_filing_id
order by Earlist_grant_date.earliest_filing_id
;
quit;

earliest has an e in it by the way.

EDIT: you probably also don't want to use the PUT, instead apply a format. That way it's a SAS date that you can do calculations and groups on and will sort correctly. Otherwise it's a character field that's harder to work with.

min(Earlist_grant_date.publn_date) as Earliest_grant_date_final format=yymmdd10.

Re: WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

SuryaKiran — Wed, 13 Jun 2018 20:17:01 GMT

You need to change two things here:

You may also got another warning: WARNING: INTO clause is ignored in the CREATE TABLE statement. If your using the INTO for creating macro variables then avoid create table statement. Your macro varibles will not be created if you have create table.

Your query returns 2 columns, so you need to mention two maco variables name in INTO. If multiple rows are returned then you need to use INTO: Name separated by "," , : Weight separated by ","

proc sql;
select Name,Weight INTO:Name,:Weight from sashelp.class
where name='James'
;
quit;

%PUT "Weight for &Name id &Weight";

proc sql;
select Name,Weight INTO:Name separated by ',',:Weight separated by ',' from sashelp.class
;
quit;

%PUT " &Name and respective weights &Weight";

Re: WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

PGStats — Wed, 13 Jun 2018 22:41:16 GMT

You probably mean:

proc sql;

create table step1.earlist_grant_date_earlist_id as
SELECT 
Earlist_grant_date.earliest_filing_id, 
put(min(Earlist_grant_date.publn_date),YYMMDDd10.) as Earlist_grant_date_final
from Step1.Earlist_grant_date as Earlist_grant_date
group by Earlist_grant_date.earliest_filing_id
order by Earlist_grant_date.earliest_filing_id
;
quit;

into: is for creating macro variables, it uses a different syntax.

Re: WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

Tom — Thu, 14 Jun 2018 00:41:27 GMT

To change (or supply) a name for the column you can just list it after the column spec, or prefix it with AS keyword..

create table step1.earlist_grant_date_earlist_id as
SELECT 
  Earlist_grant_date.earliest_filing_id
, put(min(Earlist_grant_date.publn_date),YYMMDDd10.) AS Earlist_grant_date_final
from Step1.Earlist_grant_date as Earlist_grant_date
group by Earlist_grant_date.earliest_filing_id
order by Earlist_grant_date.earliest_filing_id
;

INTO is for creating macro variables