after a proc sql create table with a primary key , how do you do an import

[andy_wk]

Hi,

I've created a table with a primary key on 2 columns. I need to import a file's contents inside of that table. I've parsed the internet but I did not find anything relevant to do an import with columns name and primary keys.

I've got almost 2 Gb of data and I don't want to change my script with insert.

Do you have any ideas, please?

[PGStats]

Can you explain what you mean by "I don't want to change my script with insert" ?  Seems to me that INSERT is the SQL way to insert new data into an existing table. - PG

[Patrick]

PROC APPEND would do the job and also allow for Bulk Load depending on where your target table is stored.

You need to better describe what you have, what you need, and what you've already done that is working/not working in order for us to give you better advice.

- Where is your target table stored (SAS or a data base)?

- How does your source data look like (SAS table, data base staging table, "raw" data)?

- Are the columns in source and target the same?

- etc.

[andy_wk]

Hi Everyone,

Thanks for the answer.

@PGStats : I have a file with record. The file is close to 2Gb. The records are like that `12121111;BigBoss;google.com;McDo,BK,ChipoGrill,Tacos,Tieboudiene`

I do not see myself doing an insert for the millions of lines, I have

@Patrick: The tables are stored in a sas, located on a server. The line look like this

`12121111;BigBoss;google.com;McDo,BK,ChipoGrill,Tacos,Tieboudiene` and the table structure is like this

proc sql;
CREATE TABLE heuri.table_repo cookie (varchar(100)),
  partner (varchar(255)),
  url (varchar(255)),
  kpages (varchar(255))
  constraint pk_ok_pa primary key(cookie,partner);
quit;

[RW9]

With regards to insert there is no need to specify each row of data:

proc sql;
  create table HAVE as
  select  *
  from    SASHELP.CLASSFIT
  where   1=0;
quit;

proc sql;
  insert into HAVE
  select  *
  from    SASHELP.CLASSFIT;
quit;

[andy_wk]

Hi RW9,

Long time no see , mate.

Indeed, the idea is not bad , at all.

I can do a basic import and then, from that do an insert select to get what I want.

Let me have a try.

[Patrick]

I would use Proc Append over SQL Insert as it's faster.

proc sql;

  create table work.CLASS( label='Student Data' bufsize=65536 )

    (

    Name char(8),

    Sex char(1),

    Age num,

    Height num,

    Weight num,

    constraint pk_name primary key(name)

    );

quit;

proc append base=work.class data=sashelp.class force;

run;

proc contents data=work.class;

run;quit;

[jakarman]

And than recreate the needed indexes afterwards.

[Patrick]

Proc Append fully maintains all table attributes, also indexes and constraints.

[jakarman]

Base SAS(R) 9.3 Procedures Guide, Second Edition (proc datasets append statement) yep you are right. But for those exceptions, when being hit by them removing and rebuilding solves a lot.
One of those exceptions is that with referential integrity contraints it will fall back on v6 method adding a n index one by one (my lockin).

[andy_wk]

Hey everyone,

Thanks for your help. However, the proc append is only in the case where you have a table ready. I do have a table with the constraints but I have a file I need to import a file in that table with constraints.

Should I do the usual import with a proc import? Because to what I read, the append is not for files... or am I wrong?

[Patrick]

Step 1: Read external file into SAS table

Step 2: Load SAS table into target table (the one with your indexes).

[Tom]

Your description of the problem seems confusing. It sounds like what you mean is that you have a text file that you want to read and insert the records into an existing table.  You also mentioned the size of the data so perhaps your actual question is how can you do this without making multiple copies of the data.  You could use a data step view to read the data and append that to you existing table.

data new / view=new ;

   infile 'my_text_file' ;

   input var1 var2 var3 ... ;

run;

proc append data=new base=mylib.mytable ;

run;