BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
It's standard SQL, just check online doc Base SAS procedures.
Data never sleeps

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20
You can take control if what's being appended by using length= in the SQL select clause.
Data never sleeps
buechler66
Barite | Level 11
Sounds promising. Can you point me to an example of this syntax?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

buechler66
Barite | Level 11

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
buechler66
Barite | Level 11

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.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

LinusH
Tourmaline | Level 20
It's standard SQL, just check online doc Base SAS procedures.
Data never sleeps
Ksharp
Super User

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

OPTIONS VARLENCHK=NOWARN ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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