About buechler66

buechler66 · ‎06-13-2016

Cool. I don't understand it tho. What is _n_queries_? _n_? nobs=_n_queries_ point=_n_?

buechler66 · ‎06-13-2016

But this doesn't help me to do the desired looping, right? Ulitmately I want to do something fancier. This obviously doesn't work, but I was thinking something like this??? Macro MyZips(zip5= ); Data Want; for i=1 to (however many RuleOrder records there are) do; proc sql; create table QueryData&zip5 as select imb_code from scan_table where rule_order = &rule_order and imb_dlvry_zip_5 = &zip5; ); quit; end do; run; %mend MyZips; data _null_; set SampledZips; call execute('%MyZips(zip5='||zip5||');'); run;

buechler66 · ‎06-13-2016

Just when I thought I had my nested looping problems solved I figured out it wasn't working the way I thought. I'm hoping for some help on structuring a sample program. I need to loop thru each ZIP5 record from the SampledZips dataset. And for each ZIP5 I also need to loop thru each Rule_Order in the QueryRules dataset and do various processing. Can anyone help me with this type of Nest Loop structure? If I can see an example I'm pretty sure I'd be able to build on it. Any examples would appreciated more than you could know. data SampledZips; input zip5 $; datalines; 00926 02360 ; run; data QueryRules; input rule_order $ datalines; 1 2 3 4; run;

buechler66 · ‎06-13-2016

Is there a function I can use within an RSubmit block to pass my Macro Variable to my local session. I need to reference the same Macro Variable after my EndRSubmit. Any help would be appreciated.

buechler66 · ‎06-13-2016

Thanks so much for your time.

buechler66 · ‎06-13-2016

Hooray! 372 proc sql; 373 create table WANT as 374 select A.ZIP5, 375 B.RULE_ORDER 376 from SAMPLEDZIPS A 377 full join QUERYRULES B 378 on 1 = 1; NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. NOTE: Table WORK.WANT created, with 18 rows and 2 columns. 379 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1927.42k OS Memory 17552.00k Timestamp 06/13/2016 12:22:43 PM 380 381 %macro test(zip5= , rule_order= ); 382 %put HERE &ZIP5 &RULE_ORDER; 383 %mend test; 384 data _null_; 385 set Want; 386 call execute('%test(zip5='||zip5||', rule_order='||rule_order||');'); 387 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 386:56 HERE 00926 4 HERE 00926 3.5 HERE 00926 3.3 HERE 00926 3.2 HERE 00926 3.1 HERE 00926 3 HERE 00926 2 HERE 00926 1.5 HERE 00926 1 HERE 02360 4 HERE 02360 3.5 HERE 02360 3.3 HERE 02360 3.2 HERE 02360 3.1 HERE 02360 3 HERE 02360 2 HERE 02360 1.5 HERE 02360 1 NOTE: There were 18 observations read from the data set WORK.WANT.

buechler66 · ‎06-13-2016

Hmm...doesn't seem to like it... 53 proc sql; 54 create table WANT as 55 select A.ZIP5, 56 B.RULE_ORDER 57 from SAMPLEDZIPS A 58 full join QUERYRULES B; - 73 76 ERROR 73-322: Expecting an ON. ERROR 76-322: Syntax error, statement will be ignored. 59 quit; NOTE: The SAS System stopped processing this step because of errors.

buechler66 · ‎06-13-2016

The SampledZips dataset looks like this: ZIP5 00926 02360 The QueryRules dataset looks like this: Rule_Order 1 2 3 4 5 6 7 8 9 The desired output to my log in the Put statement is: ZIP5 Rule_Order 00926 1 00926 2 00926 3 00926 4 00926 5 00926 6 00926 7 00926 8 00926 9 02360 1 02360 2 02360 3 02360 4 02360 5 02360 6 02360 7 02360 8 02360 9

buechler66 · ‎06-13-2016

Log output below. It did Put the text 'HERE' followed by the Rule_Order, but &ZIP5 must be blank as it doesn't appear in the output. For example: HERE 4 Log output: NOTE: CALL EXECUTE generated line. 1 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISMATCH 2 + , rule=NVL(A.ACTUAL_ENTRY_DATETIME,SYSDATE) <> NVL(B.ACTUAL_ENTRY_DATETIME,SYSDATE) 3 + 4 + 5 + 6 + 7 + , rule_order= 4); HERE 4 8 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISSING IN IV 9 + , rule=A.ACTUAL_ENTRY_DATETIME IS NULL AND B.ACTUAL_ENTRY_DATETIME IS NOT NULL 10 + 11 + 12 + 13 + 14 + , rule_order= 3.5); HERE 3.5 15 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN IV 16 + , rule=A.LAST_SCAN_DATETIME IS NULL AND B.LAST_SCAN_DATETIME IS NOT NULL 17 + 18 + 19 + 20 + 21 + , rule_order= 3.3); HERE 3.3 22 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN BIDS 23 + , rule=A.LAST_SCAN_DATETIME IS NOT NULL AND B.LAST_SCAN_DATETIME IS NULL 24 + 25 + 26 + 27 + 28 + , rule_order= 3.2); HERE 3.2 29 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN IV 30 + , rule=A.LAST_SCAN_DATETIME > B.LAST_SCAN_DATETIME 31 + 32 + 33 + 34 + 35 + , rule_order= 3.1); HERE 3.1 36 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN BIDS 37 + , rule=A.LAST_SCAN_DATETIME < B.LAST_SCAN_DATETIME 38 + 39 + 40 + 41 + 42 + , rule_order= 3); HERE 3 43 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISMATCH 44 + , rule=NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <> NVL(B.ACTUAL_DLVRY_DATE,SYSDATE) 45 + 46 + 47 + 48 + 49 + , rule_order= 2); HERE 2 50 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE LATER IN IV 51 + , rule=A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE 52 + 53 + 54 + 55 + 56 + , rule_order= 1.5); HERE 1.5 57 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISSING IN IV 58 + , rule=A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL 59 + 60 + 61 + 62 + 63 + , rule_order= 1); HERE 1 2 + %BuildZIP5QueryData(zip5=02360 ); NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 3:94 NOTE: There were 9 observations read from the data set WORK.QUERYRULES. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 715.50k OS Memory 12680.00k Timestamp 06/13/2016 11:52:54 AM NOTE: CALL EXECUTE generated line. 1 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISMATCH 2 + , rule=NVL(A.ACTUAL_ENTRY_DATETIME,SYSDATE) <> NVL(B.ACTUAL_ENTRY_DATETIME,SYSDATE) 3 + 4 + 5 + 6 + 7 + , rule_order= 4); HERE 4 8 + %BuildQueryData(analysis_desc=ACTUAL ENTRY DATETIME MISSING IN IV 9 + , rule=A.ACTUAL_ENTRY_DATETIME IS NULL AND B.ACTUAL_ENTRY_DATETIME IS NOT NULL 10 + 11 + 12 + 13 + 14 + , rule_order= 3.5); HERE 3.5 15 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN IV 16 + , rule=A.LAST_SCAN_DATETIME IS NULL AND B.LAST_SCAN_DATETIME IS NOT NULL 17 + 18 + 19 + 20 + 21 + , rule_order= 3.3); HERE 3.3 22 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME MISSING IN BIDS 23 + , rule=A.LAST_SCAN_DATETIME IS NOT NULL AND B.LAST_SCAN_DATETIME IS NULL 24 + 25 + 26 + 27 + 28 + , rule_order= 3.2); HERE 3.2 29 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN IV 30 + , rule=A.LAST_SCAN_DATETIME > B.LAST_SCAN_DATETIME 31 + 32 + 33 + 34 + 35 + , rule_order= 3.1); HERE 3.1 36 + %BuildQueryData(analysis_desc=LAST SCAN DATETIME LATER IN BIDS 37 + , rule=A.LAST_SCAN_DATETIME < B.LAST_SCAN_DATETIME 38 + 39 + 40 + 41 + 42 + , rule_order= 3); HERE 3 43 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISMATCH 44 + , rule=NVL(A.ACTUAL_DLVRY_DATE,SYSDATE) <> NVL(B.ACTUAL_DLVRY_DATE,SYSDATE) 45 + 46 + 47 + 48 + 49 + , rule_order= 2); HERE 2 50 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE LATER IN IV 51 + , rule=A.ACTUAL_DLVRY_DATE > B.ACTUAL_DLVRY_DATE 52 + 53 + 54 + 55 + 56 + , rule_order= 1.5); HERE 1.5 57 + %BuildQueryData(analysis_desc=ACTUAL DELIVERY DATE MISSING IN IV 58 + , rule=A.ACTUAL_DLVRY_DATE IS NULL AND B.ACTUAL_DLVRY_DATE IS NOT NULL 59 + 60 + 61 + 62 + 63 + , rule_order= 1); HERE 1

buechler66 · ‎06-13-2016

Hi. Sorry about the length of the post. This is what I have, note the '%PUT HERE &ZIP5 &RULE_ORDER' statement. %MACRO BuildZIP5QueryData(zip5= ); * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ; %MACRO BuildQueryData(zip5= , analysis_desc= , rule= , rule_order= ); %PUT HERE &ZIP5 &RULE_ORDER; %MEND BuildQueryData; * Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ; data _null_; set QueryRules; call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');'); run; %MEND BuildZIP5QueryData; * Pass parameters from dataset to the BuildZIP5QueryData macro. Calling the macro once for each rule record in the dataset ; data _null_; set SampledZips; call execute('%BuildZIP5QueryData(zip5='||zip5||');'); run; My desire is to have &ZIP5 resolve inside the nested macro that would resolve to my %Put statement outputting the following: ZIP5 Rule_Order 00926 1 00926 2 00926 3 00926 4 00926 5 00926 6 00926 7 00926 8 00926 9 02360 1 02360 2 02360 3 02360 4 02360 5 02360 6 02360 7 02360 8 02360 9

buechler66 · ‎06-13-2016

This does suppress the error, but the &ZIP5 variable still does not resolve as needed.

buechler66 · ‎06-13-2016

Hi. I'm getting the error "WARNING: Apparent symbolic reference ZIP5 not resolved." in my log and need some help to understand how to fix the issue. My outter macro's macro variable won't resolve inside my inner macro. Any help would be greatly appreciated. (log and program below). 378 data SampledZips; 379 input zip5 $; 380 datalines; NOTE: The data set WORK.SAMPLEDZIPS has 2 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 687.35k OS Memory 12424.00k Timestamp 06/13/2016 10:20:00 AM 383 ; 384 run; 385 386 387 * Build dataset from Oracle table that contains matching rules ; 388 proc sql; 389 connect to oracle as db (user=&orauser password=&orapass path="ivasprd"); 390 create table QueryRules as 391 select * from connection to db 392 ( select analysis_desc, 393 rule, 394 rule_order 395 from ivprl.analysis_ctrl 396 where trunc(upload_create_dt) = trunc(sysdate) 397 and rule_order not in(400) 398 and rule_order < 5 399 order by rule_order desc 400 ); NOTE: Table WORK.QUERYRULES created, with 9 rows and 3 columns. 401 disconnect from db; 402 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.75 seconds user cpu time 0.03 seconds system cpu time 0.03 seconds memory 1106.05k OS Memory 12796.00k Timestamp 06/13/2016 10:20:01 AM 403 404 405 %MACRO BuildZIP5QueryData(zip5= ); 406 * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ; 407 %MACRO BuildQueryData(analysis_desc= , rule= , rule_order= ); 408 409 %PUT &ZIP5 &RULE_ORDER; 410 411 %MEND BuildQueryData; 412 * Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the 412! dataset ; 413 data _null_; 414 set QueryRules; 415 call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , 415! rule_order='||rule_order||');'); 416 run; 417 418 %MEND BuildZIP5QueryData; 419 * Pass parameters from dataset to the BuildZIP5QueryData macro. Calling the macro once for each rule record in the dataset 419! ; 420 data _null_; 421 set SampledZips; 422 call execute('%BuildZIP5QueryData(zip5='||zip5||');'); 423 run; NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 677.57k OS Memory 12424.00k Timestamp 06/13/2016 10:20:01 AM NOTE: There were 2 observations read from the data set WORK.SAMPLEDZIPS. NOTE: CALL EXECUTE generated line. 1 + * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ; * Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ; 2 + data _null_; set QueryRules; call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');'); run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 2:153 WARNING: Apparent symbolic reference ZIP5 not resolved. 2 + ; &ZIP5 4 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.5 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.3 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.2 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.1 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 2 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 1.5 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 1 NOTE: There were 9 observations read from the data set WORK.QUERYRULES. NOTE: DATA statement used (Total process time): real time 0.02 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 714.34k OS Memory 12424.00k Timestamp 06/13/2016 10:20:01 AM NOTE: CALL EXECUTE generated line. 1 +; 2 +; 3 +; 4 +; 5 +; 6 +; 7 +; 8 +; 9 +; 3 + * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ; * Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ; 4 + data _null_; set QueryRules; call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');'); run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 4:153 WARNING: Apparent symbolic reference ZIP5 not resolved. 4 + ; &ZIP5 4 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.5 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.3 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.2 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3.1 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 3 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 2 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 1.5 WARNING: Apparent symbolic reference ZIP5 not resolved. &ZIP5 1 NOTE: There were 9 observations read from the data set WORK.QUERYRULES. NOTE: DATA statement used (Total process time): real time 0.02 seconds user cpu time 0.00 seconds system cpu time 0.03 seconds memory 714.34k OS Memory 12424.00k Timestamp 06/13/2016 10:20:01 AM NOTE: CALL EXECUTE generated line. 1 +; 2 +; 3 +; 4 +; 5 +; 6 +; 7 +; 8 +; 9 +; * Create ds of sampled Zips to use as macro parameters ; data SampledZips; input zip5 $; datalines; 00926 02360 ; run; * Build dataset from Oracle table that contains matching rules ; proc sql; connect to oracle as db (user=&orauser password=&orapass path="ivasprd"); create table QueryRules as select * from connection to db ( select analysis_desc, rule, rule_order from ivprl.analysis_ctrl where trunc(upload_create_dt) = trunc(sysdate) and rule_order not in(400) and rule_order < 5 order by rule_order desc ); disconnect from db; quit; %MACRO BuildZIP5QueryData(zip5= ); * RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ; %MACRO BuildQueryData(analysis_desc= , rule= , rule_order= ); %PUT &ZIP5 &RULE_ORDER; %MEND BuildQueryData; * Pass parameters from dataset to the BuildQueryData macro. Calling the macro once for each rule record in the dataset ; data _null_; set QueryRules; call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');'); run; %MEND BuildZIP5QueryData; * Pass parameters from dataset to the BuildZIP5QueryData macro. Calling the macro once for each rule record in the dataset ; data _null_; set SampledZips; call execute('%BuildZIP5QueryData(zip5='||zip5||');'); run; .

buechler66 · ‎06-06-2016

Thank you everyone for all the ideas. I decided to bring the datasets local to SAS from both Oracle database table and am going to create indexes on each SAS dataset on IMB_Code to address my performance issues. I appreciate the time you took to help me with this.

buechler66 · ‎06-06-2016

Ok, if I go about this by extracting each database table to a SAS dataset does it make sense to also index the SAS-side datasets? Would this help in performance also since I have no choice but to perform this query for 100+ different rules?

buechler66 · ‎06-03-2016

Interesting, but then how would I do my rule comparison in the long structure? In the wide structure the variables are on the same record and the comparison would look like: if A_DELIVERY > B_DELIVERY then do... How would this look in the long structure?

Online Status	Offline
Date Last Visited	‎07-24-2020 08:25 PM

Re: Multiple Dataset Options

Multiple Dataset Options

ODS Proc Report Output Using DEFINE AREA / GROUP

Re: ODS Output to Excel - Naming the Excel Tab

ODS Output to Excel - Naming the Excel Tab

Re: SAS ODS Excel Output - Proc Report - Word Wrapping

Re: SAS ODS Excel Output - Proc Report - Word Wrapping

SAS ODS Excel Output - Proc Report - Word Wrapping

Re: Enlarging a character variable's format length

Re: Enlarging a character variable's format length

Re: Multiple Dataset Options

Re: ODS Proc Report Output Using DEFINE AREA / GROUP

Re: ODS Output to Excel - Naming the Excel Tab

Re: SAS ODS Excel Output - Proc Report - Word Wrapping

Re: Enlarging a character variable's format length

Re: SAS EG and the Output Window - Formatted Variable Output

Re: Looping and defining macro variable

Re: SAS Efficiency Help Needed

Re: RE: PROC TRANSPOSE with correct columns order

Re: Nested Looping Frustrations - I just can't see it!

Re: Nested Looping Frustrations - I just can't see it!

Nested Looping Frustrations - I just can't see it!

Macro variable created in RSubmit - How to make it a local Marco Varia...

Re: Nested Macros - Macro Variable Resolution Help

Re: Nested Macros - Macro Variable Resolution Help

Re: Nested Macros - Macro Variable Resolution Help

Re: Nested Macros - Macro Variable Resolution Help

Re: Nested Macros - Macro Variable Resolution Help

Re: Nested Macros - Macro Variable Resolution Help

Re: Nested Macros - Macro Variable Resolution Help

Nested Macros - Macro Variable Resolution Help

Re: SAS Efficiency Help Needed

Re: SAS Efficiency Help Needed

Re: Merge of two tables with same column names