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.
>proc Sql not getting executed after restarting my sas session.
There is no reason for this. It's far more likely that your where clause returns no records.
Is there any alternative of "proc sql select into :" statement in sas through which we can create the macro variable dynamically just like proc sql select into:? some result which got when i googled is to use Proc freq procedure but i don't know how will i store that value in macro variable so that i can use that in further code
Hi,
How about doing something like:
%local cnt;
%let cnt = 0;
proc Sql;
select count(*) into:cnt from Sample_Mapping where filename="&file_src1";
quit;
%put &=cnt;
?
All the best
Bart
I tried that as well but not working..
Hi,
and how about making it a bit simpler:
%local cnt;
%let cnt = 0;
proc Sql;
select count(*) into:cnt from Sample_Mapping where filename="&file_src1";
quit;
%put &=cnt;
%if %sysevalf(&cnt > 0) %then
%Do;
/*...*/
?
All the best
Bart
If the macro variable might be empty, one way to address that is to test
%if 0&cnt > 2 %then
Hi,
your first SQL procedure is not delivering any results. Consequently the macro variable cnt is not defined and SAS issues the warning
Output-: WARNING: Apparent symbolic reference CNT not resolved.
Define any variable created by SQL explicitly as local or global to solve the Problem:
%local cnt var_cnt INPUT_COLUMN_S clmn_cnt INPUT_COLUMN_M;
%IF %SYSFUNC(INPUTN(&var_cnt,2.)) GT 0 %THEN %DO;
%DO cc=1 %to &cnt.;
%local var&cc.;
%END;
[...]
- Cheers -
Add
%let cnt=0;
at the start of your macro, so you have &cnt defined even if the SQL returns no rows.
VERY STRONG HINT:
Always start with working SAS code before you try to make it dynamic in a macro.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.