topic Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28). in SAS Programming

WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

buechler66 — Thu, 05 May 2016 14:11:10 GMT

Hi. When I run my macro below multiple times I get this SAS Warning. Is there something I can do to prevent this?

NOTE: Appending WORK.QUERYDATA to WORK.FINALDATA.
WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

%macro BuildQueryData(analysis_desc= , rule= , rule_order= );

	proc sql;
	create table QueryData as 
	(       select DISTINCT %str(%')&analysis_desc.%str(%') as RULE_NM, 
			b.actual_dlvry_date as AD_DT, 
			b.imb_code, 
			&rule_order as rule_order,
			b.spm_calc_batch_date
            from iv_ora.bi_spm_piece_recon a,  bids_ora.bi_spm_piece_recon b                                                                                                                                                                                                                                                                                                                                                                                                                                                   
            where a.spm_calc_batch_date = b.spm_calc_batch_date
              and a.imb_code = b.imb_code
            and &rule
	); 
	quit;

	* Append datasets to final dataset ;
	proc append base=FinalData
 	data=QueryData force;
	run;

%mend BuildQueryData;

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

LinusH — Thu, 05 May 2016 14:12:50 GMT

You can take control if what's being appended by using length= in the SQL select clause.

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

RW9 — Thu, 05 May 2016 14:16:58 GMT

Well, my first question, as always, why do you need to do this in macro? What is the benefit of it. Where does the data come from to form those parameters? The reason I ask is that a simple change to the process will elimiate all of this. Rather than pwhy not roc sql creating a new table, which incidently you put no lengths o the variables - so allow SAS to guess the length from the max length of the string or 8 in character variables, which is why you get this mismatch, why not just insert new data into an existing table.

If you going to use SQL, its worth finding out how it works and why it does things. Now you haven't given any test data/required output to work with, so can't give any code really, but for example:

proc sql;
  create MASTER_TABLE (VAR1 char(200),VAR2 num);
quit;

proc sql;
  insert into MASTER_TABLE
  select  ABC,DEF
  from    OTHER_DATA;
quit;

Also if you have your parameter data in a dataset, ,then you don't need to do most of that code anyway, but its hard to say from wht you have provided.

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

buechler66 — Thu, 05 May 2016 14:22:31 GMT

Sounds promising. Can you point me to an example of this syntax?

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

buechler66 — Thu, 05 May 2016 14:24:20 GMT

If I understand your question, I feed the macro from a dataset like this...

* Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ;
data _null_;
	set QueryRules;
	by rule_order;
	call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');');
run;

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

LinusH — Thu, 05 May 2016 14:24:24 GMT

It's standard SQL, just check online doc Base SAS procedures.

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

RW9 — Thu, 05 May 2016 14:50:38 GMT

Yes, so something like this. First create an empty dataset, then each call of the macro insert the results into that dataset:

%macro BuildQueryData(analysis_desc= , rule= , rule_order= );
	proc sql;
  	insert into QUERYDATA  
  	select distinct %str(%')&ANALYSIS_DESC.%str(%'), 
  			   B.ACTUAL_DLVRY_DATE, 
  			   B.IMB_CODE, 
  			   &RULE_ORDER.,
  			   B.SPM_CALC_BATCH_DATE
     from  IV_ORA.BI_SPM_PIECE_RECON A,
           BIDS_ORA.BI_SPM_PIECE_RECON B                                                                                                                                                                                                                                                                                                                                                                                                                                                   
     where A.SPM_CALC_BATCH_DATE=B.SPM_CALC_BATCH_DATE
       and A.IMB_CODE=B.IMB_CODE
       and &RULE.); 
	quit;
%mend BuildQueryData;

proc sql;
  create table QUERYDATA
  (
    RULE_NM char(200),
    AD_DT num format=date9.,
    IMB_CODE char(200),
    ...
  );
quit;

data _null_;
	set QueryRules;
	by rule_order;
	call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');');
run;

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

buechler66 — Thu, 05 May 2016 17:02:55 GMT

The first block of code is working with Length= statements, but the second block is failing an Oracle Prepare Error. Any suggestions?

1506      proc sql;
1507      create table QueryData as
1508      (       select DISTINCT %str(%')&analysis_desc.%str(%') as RULE_NM length = 1200,
1509              b.actual_dlvry_date as AD_DT,
1510              b.imb_code length = 100,
1511              &rule_order as RULE_ORDER,
1512              b.spm_calc_batch_date
1513              from iv_ora.bi_spm_piece_recon a,  bids_ora.bi_spm_piece_recon b
1514              where a.spm_calc_batch_date = b.spm_calc_batch_date
1515                and a.imb_code = b.imb_code
1516              and &rule
1517      );
1518      quit;

1550 proc sql;
1551 connect to oracle as db
1551! (user=&orauser password=&orapass path="ivasprd");
1552 create table QueryData as
1553 select * from connection to db
1554 ( select 'PIECES MISSING IN IV' as RULE_NM length = 1200,
1555 actual_dlvry_date as AD_DT,
1556 imb_code length = 100,
1557 999.1 as RULE_ORDER,
1558 spm_calc_batch_date
1559 from ivprl.bi_spm_piece_bids_recon
1560 where imb_code||trunc(spm_calc_batch_date) not in(select imb_code||trunc(spm_calc_batch_date) from
1560! ivprl.bi_spm_piece_iv_recon)
1561 );
ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: select 'PIECES MISSING IN IV' as
RULE_NM length = 1200, actual_dlvry_date as AD_DT, imb_code length = 100, 999.1 as RULE_ORDER, spm_calc_batch_date from
ivprl.bi_spm_piece_bids_recon where imb_code||trunc(spm_calc_batch_date) not in(select
imb_code||trunc(spm_calc_batch_date) from ivprl.bi_spm_piece_iv_recon).
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
1562 disconnect from db;
NOTE: Statement not executed due to NOEXEC option.
1563 quit;
NOTE: The SAS System stopped processing this step because of errors.

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

Ksharp — Fri, 06 May 2016 02:43:37 GMT

Try system options varlencheck=, But I don't think that is a good idea to ignore it .

OPTIONS VARLENCHK=NOWARN ;

Re: WARNING: Variable RULE_NM has different lengths on BASE and DATA files (BASE 29 DATA 28).

RW9 — Fri, 06 May 2016 08:12:10 GMT

You are mixing up SAS and SQL. SQL is a separate language implemented mainly for databases. SAS has the SQL engine and you can use it fine in SAS mixing in SAS. However your second statement is sending the SQL to the database, via Pass-Through. The SQL you pass through has to be valid SQL for the database your sending it to, and length isn't going to be valida there are SAS/Databases are different. Get your data out of Oracle, in the first instance, elsewhere, and store it to a SAS dataset. Then you can use either Base SAS or SAS's SQL implementation to process it.