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.
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.
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.
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
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.
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";
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.