DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

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

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.


Accepted Solutions
Solution
a week ago
Super User
Posts: 23,323

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

[ Edited ]

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.

View solution in original post


All Replies
Respected Advisor
Posts: 2,833

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

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.

--
Paige Miller
Contributor
Posts: 45

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

Posted in reply to PaigeMiller

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

Solution
a week ago
Super User
Posts: 23,323

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

[ Edited ]

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.
Valued Guide
Posts: 560

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

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";

 

 

Thanks,
Suryakiran
Esteemed Advisor
Posts: 5,482

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

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.

 

PG
Super User
Super User
Posts: 7,938

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

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 95 views
  • 4 likes
  • 6 in conversation