BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
France
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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
France
Quartz | Level 8

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

Reeza
Super User

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.
SuryaKiran
Meteorite | Level 14

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
PGStats
Opal | Level 21

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
Tom
Super User Tom
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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