append files

Hi

I don't have a great knowledge of SAP, but assuming you have a libname 'SAPLIB' assigned, there are two ways to do this:

1.  The easy / lazy way (using the colon modifier - SAS 9.2)

DATA all_files;
  set SAPLIB.aa_text:;
RUN;

2.  Get the actual names using the SQL DICTIONARY tables, and using macros:

PROC SQL noprint;
  select memname into : var_names separated by ' ' from dictionary.tables
     where memname like 'AA_TEXT%' and libname = 'SAPLIB' and dbms_memtype = 'TABLE';
%put &var_names;

/* note: in the SQL dictionary tables, all table names etc must be in CAPS */

DATA all_files;  
  SET &var_names;
RUN;

The issue is data in source is in pipe and some columns are blank

aa|bb||||cc

Hi

Ok.  I don't know that I fully understand the question.  But here goes...

If you are importing pipe-delimited files, then there are a few options on the FILENAME  & INFILE statement that can be done to help solve your problem:

You can use wildcards in the FILENAME statement to read multiple files, and the DSD option on the INFILE statement to identify double pipes as blank values


FILENAME pppp "c:\docs\aa_text*.txt";

DATA All_files;
INFILE pppp DLM= '|' DSD;
INFORMAT Var1 Var2 Var3 $100.;
INPUT Var1 Var2 Var3;
RUN;

i guess i have to be bit clear data in sap lib looks like libname  /aaa/sap
aa_text_20110401 (file 1)
aa|bb|cc||ee||ff
bb|cc|kk|222|fff

aa_text_20110408 (file 2) sumtime we have blank fields allover and in some cases they are ok
cc||gg|666|ff
dd|kk||||


i want to append all 10 files so data should look like so make one big file
aa|bb|cc||ee||ff
bb|cc|kk|222|fff
cc||gg|666|ff
dd|kk||||

OR can we read one by one file from SAP i mean some kind of loop and then insert data in sas db?

Hi Theart,
I am thinking unix script will be good for this as it has to match another file for counts too
now can you please tell me if i want to call a unix script sumthing like below from
where i can call it i eman which transformer to put that in

for file in $(ls -1 test_aa_*); do
   wc -l $file
   [[ $(wc -l < $file) -eq $(cat ${file}.cntrl) ]] && cat $file >> test_aa_full.txt
 done
wc -l test_aa_full.txt

Hi Sam

I won't be any help with Unix scripts... sorry,

Perhaps you can send me the log from the code I gave you... and lets see why my solution above isn't working...

If this is going to be an ongoing requirement then perhaps you want to have your employer look into obtaining either SAS/Access Interface to R/3 or the SAS/Access Interfact to SAP BW.

If the files you're trying to load into SAS are flat-file extracts from SAP, then the Filename Data Step approach that theartfuldazzler suggests should work just fine and taking a script approach is completely unnecessary... reading delimited files is well-understood and there is plenty of documentation.