append files

i have like 10 sap files aa_text_20110401,aa_text_20110407,aa_text_20110429 and so on i want to append all these files and load in sas database
how can i do it and also this is not just one time it will happens everyday but files number might be different

Thanks
sam2929Asked:
Who is Participating?
 
theartfuldazzlerCommented:
Hi Sam

The code below should work - it's just a different OS (different from Windows....)

FILENAME pppp "/aaa/sap/aa_text*";

DATA All_files;
INFILE pppp DLM= '|' DSD TRUNCOVER;
INFORMAT Var1 Var2 Var3 $100.;
INPUT Var1 Var2 Var3;
RUN;
0
 
theartfuldazzlerCommented:
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;

0
 
sam2929Author Commented:
The issue is data in source is in pipe and some columns are blank

aa|bb||||cc
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
theartfuldazzlerCommented:
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;



0
 
sam2929Author Commented:
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?




0
 
sam2929Author Commented:
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
0
 
theartfuldazzlerCommented:
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...

0
 
d507201Database Marketing ConsultantCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.