?
Solved

append files

Posted on 2011-05-03
8
Medium Priority
?
690 Views
Last Modified: 2013-11-16
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
0
Comment
Question by:sam2929
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 35657484
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
 

Author Comment

by:sam2929
ID: 35687616
The issue is data in source is in pipe and some columns are blank

aa|bb||||cc
0
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 35687676
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:sam2929
ID: 35688440
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
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 2000 total points
ID: 35688476
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
 

Author Comment

by:sam2929
ID: 35689115
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
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 35689448
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
 
LVL 7

Expert Comment

by:d507201
ID: 35690930
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question