topic Re: Proc Sql code not Running Inside Sas Macro code After Restarting My SAS Session in SAS Programming

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

Shubham5497 — Mon, 17 Feb 2020 09:59:40 GMT

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.

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

ChrisNZ — Mon, 17 Feb 2020 10:37:03 GMT

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

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

Shubham5497 — Mon, 17 Feb 2020 11:08:11 GMT

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

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

yabwon — Mon, 17 Feb 2020 11:07:13 GMT

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

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

Shubham5497 — Mon, 17 Feb 2020 11:18:10 GMT

I tried that as well but not working..

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

Oligolas — Mon, 17 Feb 2020 11:50:16 GMT

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

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

Kurt_Bremser — Mon, 17 Feb 2020 11:52:19 GMT

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.

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

yabwon — Mon, 17 Feb 2020 12:04:44 GMT

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

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

ChrisNZ — Mon, 17 Feb 2020 20:18:30 GMT

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

%if 0&cnt > 2 %then