DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

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

[ Edited ]

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;

 


Accepted Solutions
Solution
‎05-06-2016 09:29 AM
Super User
Posts: 5,256

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

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

View solution in original post


All Replies
Super User
Posts: 5,256

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

You can take control if what's being appended by using length= in the SQL select clause.
Data never sleeps
Regular Contributor
Posts: 212

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

Sounds promising. Can you point me to an example of this syntax?
Super User
Super User
Posts: 7,401

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

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.

Regular Contributor
Posts: 212

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

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;
Super User
Super User
Posts: 7,401

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

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;
Regular Contributor
Posts: 212

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

[ Edited ]

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.

 

 

Super User
Super User
Posts: 7,401

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

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.

Solution
‎05-06-2016 09:29 AM
Super User
Posts: 5,256

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

It's standard SQL, just check online doc Base SAS procedures.
Data never sleeps
Super User
Posts: 9,681

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

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

OPTIONS VARLENCHK=NOWARN ;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1033 views
  • 4 likes
  • 4 in conversation