05-05-2016 10:10 AM - edited 05-05-2016 10:11 AM
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;
05-05-2016 10:12 AM
05-05-2016 10:16 AM
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.
05-05-2016 10:24 AM
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;
05-05-2016 10:50 AM
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;
05-05-2016 01:00 PM - edited 05-05-2016 01:02 PM
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.
05-06-2016 04:12 AM
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.
05-05-2016 10:43 PM
Try system options varlencheck=, But I don't think that is a good idea to ignore it .
OPTIONS VARLENCHK=NOWARN ;
Need further help from the community? Please ask a new question.