/******************/
/* Dataset */
/******************/
%Macro ImportData_BABC(result_file,output_dataset);
/* %let filename=%sysfunc(catx(\,&path1,&result_file));*/
%let filename=%sysfunc(catx(\,&path1,&file_name));
%put 'entered loop ImportData file_name' &filename.;
/* %let file_name_new=%sysfunc("'"||STRIP('&filename')||"'");*/
/* %put 'entered loop - file_name_new ' &file_name_new.;*/
/* data _null_;*/
/* call symput("file_name_new","'"||STRIP(&filename)||"'");*/
/* call symput ('file_name_new',"'"||STRIP('&filename')||"'");*/
/* run;*/
/* %put 'entered loop - file_name_new ' &file_name_new.;*/
%if %sysfunc(fileexist(&filename)) %then
%do;
filename holdit " In\Temp\Hold\ test1.txt" lrecl=32766;
/* filename in_file pipe %sysfunc(quote(dir "&filename." /b));*/
filename in_file pipe "dir ""&filename."" /b";
/* Reading in the .csv file and creating a temp .txt file */
/* After Writing out the first row with Column Names will write the second row with '*'(asterix) */
/* Asterix will be equal to the number of column headers present in File */
/* Open filename 'holdit' (test1.txt) */
/* Action: Need to add a step to remove the strange character that appears within the text. Will do that on return from vac*/
data _null_;
length string $4000;
file holdit LRECL=32767;
infile "&filename" lrecl=32767 end = done;
input;
put _infile_;
wcount = countw(_infile_,',');
string =('*'||repeat(',*',wcount-2));
put string;
do until(done);
input;
put _infile_;
end;
stop;
run;
/* this step release the input file */
filename in_file;
%put '********** 1 ***';
proc import
datafile=holdit out=work.temp DBMS=CSV replace;
/* datafile="&path1\&rtcf_file." out=work.&output_file. dbms=xlsx replace;*/
guessingrows=444;
getnames=yes;
run;
/* Setting all the Variable to a default var length of 4000 */
filename tmp "\Temp\Hold\test3.txt" lrecl=32766;
filename holdit;
data _null_;
set work.temp end=eod;
array chrvar {*} $1000 _character_;
array len{1000} _temporary_;
do c=1 to dim(chrvar);
len{c}=max(4000,length(chrvar{c}));
end;
file tmp;
if eod then
do c=1 to dim(chrvar);
vnam=vname(chrvar{c});
put vnam '$' len{c};
end;
run;
data work.want;
length %include tmp ;;
set work.temp;
run;
/* If table does exist */
%if %sysfunc(exist(caciamaz.&output_dataset.)) %then
%do;
data work.&output_dataset.;
format ResultsLoadDate date9.;
set work.want(firstobs=2);
/*varname = compress(translate(REASON_FOR_SCORE, "'~~", "’"), '~');*/
Result_file=symget('file_name');
ResultsLoadDate="&file_upload_date."d;
run;
/* DATA caciamaz.&output_dataset.;*/
/* DO UNTIL (lastrec);*/
/* SET caciamaz.&output_dataset. work.&output_dataset. end = lastrec;*/
/* OUTPUT;*/
/* END;*/
/**/
/* RUN;*/
data caciamaz.&output_dataset.;
set caciamaz.&output_dataset. work.&output_dataset.;
run;
%put 'helleeeee';
/* %sysexec move "&filename" "&path1\ArchiveProcessed";*/
%end;
%else
%do;
/* If table does not exist */
data work.&output_dataset.;
length Result_file $200.;
format ResultsLoadDate date9.;
set work.want(firstobs=2);
/* varname = compress(translate(REASON_FOR_SCORE, "'~~", "’"), '~');*/
Result_file=symget('file_name');
ResultsLoadDate="&file_upload_date."d;
run;
data caciamaz.&output_dataset.;
set work.&output_dataset.;
run;
%end;
%end;
%Mend ImportData_BABC;
File 1 ------ %ImportData_BABC('file1.csv',results_hi_babc_med_in) File 2 ------
%ImportData_BABC('file2.csv',results_hi_babc_med_in)
The above code works but when I pass input file to be read dynamically( when there is more than one file) the sas session does not exit or stop.
this is the code to pass file dynamically
/* Read in all files from FTP In directory */
filename infile pipe "dir "&wash_file." /b";
data input_file;
length file_name $200;
format file_upload_date date9.;
infile infile truncover;
input file_name $50.;
file_upload_date=input(scan((scan(file_name,-1,'_')),1,'.'),YYMMDD10.);
actual_file_name=upcase(substr(file_name,1,(length(file_name)-length(scan(file_name,-1,'_')))-1));
/* call symput('file_upload_date',put(file_upload_date,DATE9.));*/
call symput ('file_name',file_name);
run;
proc sort data=input_file nodupkey;
by actual_file_name file_upload_date;
run;
%put &file_upload_date.;
%put &file_name.;
/*Count number of records of filenames in dataset*/
data _null_;
%global records;
set input_file end=last;
if last then call symputx("Records",put(_n_,8.)); /*_n_ is observation number in sas dataset*/
run;
%put &Records.;
/* End Import Files by Stream */
/* Main Logic - Reading result file based on the stream */
%macro Import_File(records);
%if &records. > 0 %then
%do;
/* %do i=1 %to &records.; */
%put 'enterd loop 1';
data _null_;
set input_file;
call symput ('file_name',strip(file_name));
when ('BABC_EXPORT') rc=dosubl('%ImportData_BABC(&file_name,results_hi_babc_med_in)');
otherwise;
end;
STOP;
%put 'exited loop 1';
run;
%end;
%else
%do;
%put 'loop 6';
/* data _null_;*/
/* rc=dosubl('%folder_empty_message()');*/
/* run;*/
%put 'exited loop 6 ';
%end;
%mend;
thus code was working before when I had the files that had to be read properly declared ie. using prc import I had all the columns defined.
But now I want to NOT define columns and read the file.. Hence changed the first part of the code. I am trying to re-use the part that dynamically passes the file but it is not stopping after all the files that have been read.
I need the code to exit gracefully.
The logic is working but it just hangs after the files are read. I hope I am making sense.
Thanks.
... View more
