Create & insert into table

[ColleenCB]

Hi all,

I am trying to create a table and insert data into it in a Proc SQL.

The problem is that the fields are unknown until the query has run.

Sql uses "into BI_Dbrd.SAS_BIDP037_Supervisor_Rpt" and then creates the new table with the fields loaded.

I can only find "insert into BI_Dbrd.SAS_BIDP037_Supervisor_Rpt " but the table must exist already

This is my code

Proc SQL outobs = 2;
/*insert into BI_Dbrd.SAS_BIDP037_Supervisor_Rpt */
Create table work.checking as
select e.employeekey
,e.Name as First_Name "First Name"
,e.Surname
,o.Division
,o.Segment
,o.Businessunit as Business_Unit "Business Unit"
into BI_Dbrd.dbo.SAS_BIDP037_Supervisor_Rpt
From BI_CWH.Factcurrentemployeecount f
join BI_CWH.Dimemployee e on e.dimemployeeid = f.dimemployeeid
join BI_CWH.DimOrganisationManagement o on o.DimOrganisationManagementID = f.DimOrganisationManagementID

Where f.MonthKey = (Select Value from BI_CWH.Control Where DataFlow = "Oracle HRMS")
and f.HeadCount = 1;
Quit;

The error I get is: 

Can someone please assist?

Have a lovely day

[Kurt_Bremser · Accepted solution]

You either must do a CREATE TABLE or a INSERT INTO statement.

As a part of a SELECT clause, INTO is used to store values into macro variables (that's why it expected a colon there, followed by the macro variable name).

This is sufficient to create your new table in WORK:

proc sql outobs = 2;
create table work.checking as
  select
    e.employeekey
    ,e.Name as First_Name "First Name"
    ,e.Surname
    ,o.Division
    ,o.Segment
    ,o.Businessunit as Business_Unit "Business Unit"
  from BI_CWH.Factcurrentemployeecount f
  join BI_CWH.Dimemployee e on e.dimemployeeid = f.dimemployeeid
  join BI_CWH.DimOrganisationManagement o on o.DimOrganisationManagementID = f.DimOrganisationManagementID
  where
    f.MonthKey = (select Value from BI_CWH.Control where DataFlow = "Oracle HRMS")
    and f.HeadCount = 1
;
quit;

[ColleenCB]

Thank you.
I had to change "work." to our server, and the name was also longer than 30 characters.
The name I can change in Sql.
This saves a lot of time.