Proc Sql code not Running Inside Sas Macro code After Restarting My SAS Session

[Shubham5497]

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.

[ChrisNZ]

>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.

[Shubham5497]

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

[yabwon]

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

[Shubham5497]

I tried that as well but not working..

[yabwon]

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

[ChrisNZ]

If the macro variable might be empty, one way to address that is to test

  %if 0&cnt > 2 %then 

[Oligolas]

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;
[...]

[Kurt_Bremser]

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.