BookmarkSubscribeRSS Feed
andy_wk
Calcite | Level 5

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?

12 REPLIES 12
PGStats
Opal | Level 21

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

PG
Patrick
Opal | Level 21

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
Calcite | Level 5

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 Smiley Happy

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

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
Barite | Level 11

And than recreate the needed indexes afterwards.

---->-- ja karman --<-----
Patrick
Opal | Level 21

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

jakarman
Barite | Level 11

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

---->-- ja karman --<-----
andy_wk
Calcite | Level 5

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

Step 1: Read external file into SAS table

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

Tom
Super User Tom
Super User

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 6251 views
  • 1 like
  • 6 in conversation