Multiple files to load into different tables


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Multiple files to load into different tables
# 1  
Old 04-26-2014
Multiple files to load into different tables

multiple files to load into different tables,


I have a script show below, but this script loads data from txt file into a table,
but i have multiple input files(xyzload.txt,xyz1load.txt,xyz2load.txt......) in the unix folder ,
can we load these files in diff tables (table 1, table2 .............) in load.
or
can we load this files in the same table with the file name as another column


-----------------script-----------------


Code:
Code:
proc printto log="/test/userabc/xyz.log" new; run;

data temp;
%let _EFIERR_ = 0; 
filename bobj ('/test/xyzload.txt');

infile rma
delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=1 ; 

informat col1 $18.;
informat col2 $20.;
informat col3 $20.;
informat col4 $5.;
informat col5 $5.;
informat col6 $5.;
informat col7 $20.;
informat id $12.;

input
col1 $
col2 $
col3 $
col4 $
col5 $
col6 $
col7 $
id $
;

format col1 $18.;
format col2 $20.;
format col3 $20.;
format col4 $5.;
format col5 $5.;
format col6 $5.;
format col7 $20.;
id=_n_;

if _ERROR_ then call symput('_EFIERR_',1);
run;

data lbe_temp;
set temp;
keep
col1
col2
col3
col4
col5
col6
col7
id
;
run;


libname xxxx  user=&uid. pass=&pwd. database="jump" server=jumpper1;
run;

data td.table1(tenacity = 5 sleep = 1 dbcreate_table_opts='PRIMARY INDEX(id,col1)' dbcommit=1000000 fastload=yes);
set lbe_temp;
run;

proc sql _method feedback;
proc printto; run;


Last edited by bartus11; 04-26-2014 at 06:50 PM.. Reason: Please use [code][/code] tags.
# 2  
Old 04-26-2014
nani1984,
This looks more like a SAS question than a Unix question. I'm sure if you post in SAS forum you will get a better response but I will give some suggestions based on my older experience with SAS.

1. Looks like the sole purpose of the below datastep is to keep only select variables. Instead of reading the entire data set again
and creating another data step, why not just put KEEP= on the data temp when you are creating that data set?
Code:
data lbe_temp;
set temp;
keep
col1
col2
col3
col4
col5
col6
col7
id
;
run;

Code:
data temp (KEEP=col1, col2, col3, col4, col5, col6, col7, id);

2. To read in multiple files, you can use pipe statement.

Code:
filename myfile pipe “ls -1” xyz*load.txt

Once you read in files, you can then loop thru them and load the file names, usually with a macro, into you tables as you require.
See below links:

http://www.nesug.org/proceedings/nesug05/ap/ap14.pdf
http://www.wuss.org/proceedings12/55.pdf
https://support.sas.com/techsup/technote/ts581.pdf
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Export Oracle multiple tables to multiple csv files using UNIX shell scripting

Hello All, just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table. Can you please suggest why? or any better idea? export FILE="/abc/autom/file/geo_JOB.csv" Export= `sqlplus -s dev01/password@dEV3... (16 Replies)
Discussion started by: Hope
16 Replies

2. UNIX for Dummies Questions & Answers

Load multiple files into a table

Hi, I need to load data from two files to a single table. My requirement is that I get two files in which a few column data are manadatory. These files are identified based on the file name. For example, I have two files ABCFile and BCDFile. ABCFile has mandatory data in column 3 and 4... (0 Replies)
Discussion started by: reshma15193
0 Replies

3. Shell Programming and Scripting

Append data by looking up 2 tables for multiple files

I want to lookup values from two different tables based on common columns and append. The trick is the column to be looked up is not fixed and varies , so it has to be detected from the header. How can I achieve this at once, for multiple data files, but lookup tables fixed. The two lookup... (5 Replies)
Discussion started by: ritakadm
5 Replies

4. Shell Programming and Scripting

Multiple files to load into different tables,

multiple files to load into different tables, I have a script show below, but this script loads data from txt file into a table, but i have multiple input files(xyzload.txt,xyz1load.txt,xyz2load.txt......) in the unix folder , can we load these files in diff tables (table 1, table2... (0 Replies)
Discussion started by: nani1984
0 Replies

5. Shell Programming and Scripting

merge multiple tables with perl

Hi everyone, I once again got stuck with merging tables and was wondering if someone could help me out on that problem. I have a number of tab delimited tables which I need to merge into one big one. All tables have the same header but a different number of rows (this could be changed if... (6 Replies)
Discussion started by: TuAd
6 Replies

6. Web Development

mysql query for multiple columns from multiple tables in a DB

Say I have two tables like below.. status HId sName dName StartTime EndTime 1 E E 9:10 10:10 2 E F 9:15 10:15 3 G H 9:17 10:00 logic Id devName capacity free Line 1 E 123 34 1 2 E 345 ... (3 Replies)
Discussion started by: ilan
3 Replies

7. Programming

SQL Add to Multiple Tables

I'm pretty new to the database world and I've run into a mental block of sorts. I've been unable to find the answer anywhere. Here's my problem: I have several tables and everything is as normalized as possible (as I've been lead to understand normalization.) Normalization has lead to some... (1 Reply)
Discussion started by: flakblas
1 Replies

8. Shell Programming and Scripting

Reading data from multiple tables from Oracle DB

Hi , I want to read the data from 9 tables in oracle DB into 9 different files in the same connection instance (session). I am able to get data from one table to one file with below code : X=`sqlplus -s user/pwd@DB <<eof select col1 from table1; EXIT; eof` echo $X>myfile Can anyone... (2 Replies)
Discussion started by: net
2 Replies

9. Shell Programming and Scripting

IBM Informix Load and Insert with multiple files

Hi , Can you guys please help as I have list of files xaa, xab, xac.........xza for eg in which to perform load the 1st (xaa) and insert into table, then only proceed for the 2nd , 3rd and so forth. In other words, before 1st one finished, 2nd one shall not load and insert to table, and so... (0 Replies)
Discussion started by: rauphelhunter
0 Replies

10. Shell Programming and Scripting

Load Oracle tables and switch public synonym

I need some help from Oracle and UNIX expertise point of view. I have two tables, METADATA_A and METADATA_B. I need to switch loading these tables. If we load METADATA_A today, the following week we would have to load METADATA_B. There is a public synonym "METADATA" that sits on top of... (2 Replies)
Discussion started by: madhunk
2 Replies
Login or Register to Ask a Question