topic Re: after a proc sql create table with a primary key , how do you do an import in SAS Procedures

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

andy_wk — Wed, 26 Nov 2014 17:08:24 GMT

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?

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

PGStats — Wed, 26 Nov 2014 17:31:09 GMT

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

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

Patrick — Wed, 26 Nov 2014 17:42:59 GMT

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.

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

andy_wk — Thu, 27 Nov 2014 09:00:22 GMT

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;

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

RW9 — Thu, 27 Nov 2014 09:48:02 GMT

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;

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

andy_wk — Thu, 27 Nov 2014 09:52:11 GMT

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.

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

Patrick — Thu, 27 Nov 2014 20:27:12 GMT

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;

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

jakarman — Thu, 27 Nov 2014 21:55:18 GMT

And than recreate the needed indexes afterwards.

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

Patrick — Thu, 27 Nov 2014 22:01:01 GMT

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

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

jakarman — Thu, 27 Nov 2014 22:12:31 GMT

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).

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

andy_wk — Thu, 27 Nov 2014 22:19:36 GMT

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?

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

Patrick — Fri, 28 Nov 2014 00:06:26 GMT

Step 1: Read external file into SAS table

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

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

Tom — Fri, 28 Nov 2014 02:14:17 GMT

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;