Shell Script to read a record into variables

Shell Programming and Scripting
1

I am trying to write a shell script that will read from text file. There are 4 fields in the file that I need to move into variables. Those variables are then loaded to an Oracle Table.

The issue is that the 4 variables are not all populated at the time the SAS script writes to the Oracle table. It appears that only 1 of the variables is getting populated.

Here is the file layout. There are 4 fields pipe delimited

johdoe|/sas/SAS_env/users/jandoe/BHV|12/31/2026|123

Here is the shell script:

IFS="|"
for n in $(cat $IN_FILE/$FILE_NM);do
     fields=($n)
     first="${fields[0]}"
     second="${fields[1]}"
     third="${fields[2]}"
     fourth="${fields[3]}"
     echo "$first"
     echo "$second"
     echo "$third"
     echo "$fourth"

export VAR1=$first
export VAR2=$second
export VAR3=$third
export VAR4=$fourth


$SAS_BAT/sasbatch.sh $PROG_DIR/data_retention.sas -print $LOG_DIR/data_retention.lst -log $LOG_DIR/data_retention.log

done

Here is the SAS Code:

%let var1=%sysget(VAR1);
%let var2=%sysget(VAR2);
%let var3=%sysget(VAR3);
%let var4=%sysget(VAR4);

%put &var1.;
%put &var2.;
%put &var3.;
%put &var4.;

PROC SQL;

INSERT INTO JPJOYCE.DATA_RETENTION
      VALUES ("&var1.", "&var2.", &var3., &var4.);


QUIT;

This is the result:

johdoe



/sas/SAS_env/users/jandoe/BHV



12/31/2026




123
2

@joemrk3 , Welcome, as way of simple demonstration, using your supplied input.
Your code looks way too 'wordy'. Why are you doing single inserts ?

cat /tmp/joemrk3 
johdoe|/sas/SAS_env/users/jandoe/BHV|12/31/2026|123
IFS="|" read -r f1 f2 f3 f4 < /tmp/joemrk3
echo "[$f1] [$f2] [$f3] [$f4]"
[johdoe] [/sas/SAS_env/users/jandoe/BHV] [12/31/2026] [123]

Please, read the FAQ, learn how to use markdown to separate text/code from dialog before posting.

1 Like
3

welcome to the community, @joemrk3
please use markdown code tags when posting data/code samples.
I've edited your post for now, but please properly format your posts going forward.

2 Likes
4

@joemrk3 , if you or a colleague know how to use the oracle sqlloader (sqlldr) utility, then leveraging that could simplify this effort (and perhaps many others).
Investing time to get basic knowledge of tools supplied typically pays back manifold.

cat loader.ctl
--
-- example of a basic oracle sqlloader control file
--
-- refer to oracle documentation for details
-- https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader-commands.html
-- https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader-control-file-contents.html
--
-- typical invocation: sqlldr user/pwd control=loader.ctl

LOAD DATA
        INFILE '/tmp/joemrk3.txt'  -- replace with actual filename
        FIELDS TERMINATED BY '|' 
        INTO TABLE JPJOYCE.DATA_RETENTION -- replace with actual table name as required
        -- SKIP 1 -- uncomment this if there's a header row in your data
~
1 Like
5

@joemrk3, a simple awk transformation could be another possible.

cat joemark3.awk 
{ printf("insert into %s values ( \42%s\42, \42%s\42, %s, %s );\n", table, $1, $2, $3, $4) } #\42 is a double quote "

awk -F"|" -vtable=JPJOYCE.DATA_RETENTION -f joemark3.awk joemark3.txt 
insert into JPJOYCE.DATA_RETENTION values ( "johdoe", "/sas/SAS_env/users/johdoe/BHV", 12/31/2026, 123 );
insert into JPJOYCE.DATA_RETENTION values ( "janedoe", "/sas/SAS_env/users/jandoe/XYZ", 12/31/2033, 999 );
insert into JPJOYCE.DATA_RETENTION values ( "billybob", "/sas/SAS_env/users/billybob/ABC", 12/31/2030, 888 );
2 Likes
6

Thank you all for the responses. They are much simpler than what I was trying to do. Also I will l format future posts per the standards defined.

2 Likes