Hii.. I am trying to write the code which will read all the files from the specified location irrespective of the file size and No. of columns in the Source file . I'm facing an issue regarding execution of "proc Sql select into:" statement . proc Sql not getting executed after restarting my sas session. But if i commit mistake for example if i changed Inputn() to nputn() and run the macro and now if i correct my mistake nputn() to inputn() then if i run the macro then my code running and gives correct output .I'm not getting where i'm making mistake. My Entire Code is Below.... %Macro All_process(file_src1, cont1);
proc Sql;/*Not getting Executed After restarting My SAS Session */
select count(*) into:cnt from Sample_Mapping where filename="&file_src1";
quit;
%put &=cnt;
%if %SYSFUNC(INPUTN(&cnt,2.)) > 0 %then
%Do; /*if change Inputn() to nputn() run macro then if correct my mistake nputn() to inputn() then codes runs*/
Data SAMP.&file_src1._mand_column;/*Creating Table which will consist only mandatory columns of the File*/
set Samp.Sample_Mapping (WHERE=(STRIP(MANDATORY)='Y'
AND(Filename)="&file_src1"));
run;
data _null_;
set Samp.&file_src1._mand_column;
call symputx('Filename', strip(Filename));
CALL SYMPUTX('INPUT_FILE_Path', STRIP(Extract_Location) ||STRIP(Filename) ||'.csv');
Call Symputx('STG_TBL_NAME', STRIP(ST_TBL_NAME));
CALL SYMPUTX('TBL', STRIP(ST_TBL_NAME));
run;
%put &=TBL;
Data samp.&TBL._detail_frm_mapping_sheet;/*Get all columns of table from mapping sheet and their Detail*/
set Sample_mapping;
where strip(filename)="&file_src1";
run;
proc Sql;/*counting the total number of column of table from mapping Sheet*/
select Count(*) into: var_cnt from samp.&TBL._detail_frm_mapping_sheet where Column_name not in (" ","Batch_id", "Batch_date");
quit;
%IF %SYSFUNC(INPUTN(&var_cnt,2.)) GT 0 %THEN %DO;
PROC SQL;
SELECT Column_Name
INTO: Column_Name1 -: Column_Name%SYSFUNC(COMPRESS(&var_cnt)) FROM samp.&TBL._detail_frm_mapping_sheet
WHERE Column_Name NOT IN (" ", "Batch_id", "Batch_date");
SELECT Column_Name INTO: INPUT_COLUMN_S SEPARATED BY " " FROM samp.&TBL._detail_frm_mapping_sheet
WHERE Column_Name NOT IN (" ", "Batch_id", "Batch_date");
QUIT;
%END;
%Reading_source_file;
/* DATA samp.&Filename._Source;
INFILE "&INPUT_FILE_Path" LRECL=2500 DELIMITER=',' DSD MISSOVER FIRSTOBS=2;
%DO K=1 %TO &var_cnt;
ATTRIB &&Column_Name&K LENGTH=$300 FORMAT=$300. INFORMAT=$300.;
%END;
INPUT &INPUT_COLUMN_S;
RUN;
*/
proc Sql;
select Count(*) into: clmn_cnt from SAMP.&file_src1._mand_column;
quit;%IF %sysfunc(InputN(&clmn_cnt,2.0)) GT 0 %THEN %DO;
PROC SQL;
SELECT Column_Name
INTO: Column_Name1 -: Column_Name%SYSFUNC(COMPRESS(&clmn_cnt)) FROM SAMP.&file_src1._mand_column;
SELECT Column_Name INTO: INPUT_COLUMN_M SEPARATED BY " " FROM
SAMP.&file_src1._mand_column;
quit;
%end;
PROC SORT DATA = samp.&Filename._Source
OUT = &Filename._DUPVALCHECK
UNIQUEOUT = &filename._Unique_rows NOUNIKEY;
BY _ALL_;
RUN;
DATA &Filename._DUPVALCHECK;
length Source_Filename $ 256 ;
LENGTH REJECT_REASON $ 256;
SET &Filename._DUPVALCHECK;
REJECT_REASON = "Duplicate Record";
Source_Filename="&Filename";
RUN;
data &filename._Final_Valdt_Clmn_Table(drop= REJECT_REASON flag Source_Filename) Error_record_table(drop=flag) ;
length Source_Filename $ 256 ;
LENGTH REJECT_REASON $ 256;
set &filename._Unique_rows;
flag=0;
Process_Date="&Sysdate9";
%do i=1 %to&clmn_cnt;
if missing(&&Column_Name&i) then
do;
flag=flag+1;
IF REJECT_REASON EQ "" THEN
DO;
REJECT_REASON="&&Column_Name&i";
END;
ELSE
DO;
REJECT_REASON=CATS(REJECT_REASON, ',', "&&Column_Name&i");
END;
end;
else
do;
flag=flag;
end;
%end;
IF REJECT_REASON NE "" THEN
DO;
REJECT_REASON=CATX(' ',REJECT_REASON,
" Mandatory field/s contain missing values");
Source_Filename="&Filename";
END;
if flag=0 then
output &filename._Final_Valdt_Clmn_Table;
else
output Error_record_table;
run;
DATA Final_Error_Record_Table;
set &Filename._DUPVALCHECK Error_record_table;
Process_Date="&sysdate9";
run;
%end;
%else
%do;
%put "Table dosn't Exist";
%end;
%mend All_process;
%macro Reading_source_file;
DATA samp.&Filename._Source;
/**/
INFILE "&INPUT_FILE_Path" LRECL=2500 DELIMITER=',' DSD MISSOVER FIRSTOBS=2;
%DO K=1 %TO &var_cnt;
ATTRIB &&Column_Name&K LENGTH=$300 FORMAT=$300. INFORMAT=$300.;
%END;
INPUT &INPUT_COLUMN_S;
RUN;
%mend Reading_source_file;
libname SAMP Base '/sasdata/TestCode';
filename folder "/home/sasdemo/Test11/";
proc import datafile='/home/sasdemo/MappTemp1.csv' out=sample_Mapping dbms=csv
replace;
run;
Data Samp.Sample_Mapping;
set sample_mapping(where=(filename is not null) );
Mandatory=upcase(Mandatory);
ERR_LIBREF='STGERROR';
STG_LIBREF='SASSTAGE';
run;
data _null_;
list=dopen("folder");/*opening the given location folder reading all the files */
do i=1 to dnum(list);
cont1=i;
File_src=substr(trim(dread(list, i)), 1, index(trim(dread(list, i)), '.')-1);
put File_src=;
call execute('%All_process(file_src1='||strip(file_src)||',cont1='||strip(cont1)||');');
put i=;
end;
run; Output-: WARNING: Apparent symbolic reference CNT not resolved.
... View more
