HI all,
The code I'm using at the moment doesn't want to resolve the precount and count macros first time I run the code, but after running the code three times, it always is fine. But I have to run it three times before it stops erroring out. Also, it won't update the value of the precount and count macro variables when I use the PROC SQL INTO syntax. I tried making them global variables, tried setting them to initial values, nothing I do seem to work. I'm tearing my hair out a bit here, any suggestions? Here's the code:
%macro segment_volumes_obc(sub_segment_temp=, volume=); /* %global precount;*/ /* %global count;*/ /* %let precount = 0;*/ /* %let count = 0;*/ proc sql; create table segment_records_&sub_segment_temp. as select * from sel_inpt.sub_segmentation where sub_segment = "&sub_segment_temp." /* and comms_type = 'OBC'*/; quit; proc sql noprint; select count(*) into :precount from segment_records_&sub_segment_temp.; quit; %if &precount. < &volume. %then %do; proc surveyselect data = segment_records_&sub_segment_temp. method = srs n = &precount. out = segment_sample_&sub_segment_temp.; run; %end; %else %do; proc surveyselect data = segment_records_&sub_segment_temp. method = srs n = &volume. out = segment_sample_&sub_segment_temp.; run; %end; proc sql noprint; select count(*) into :count from segment_sample_&sub_segment_temp.; quit; %put "precount: ", &precount; %put "count: ", &count; %put "volume: ", &volume; %let diff = %sysevalf(&volume - &count); %put "diff: ", &diff; %mend segment_volumes_obc; %global diff; %let diff = 0; data temp; set sel_inpt.obc_segment_priorities; retain diff; call symput('sub_seg', sub_segment); diff = symget('diff'); * store the macro variable diff for later calculation; if volume ^= . then do; volume2 = sum(volume,diff); call symput('vol',volume2); *use previously stored/retained value of diff for calculation; vol = symget('vol'); *print it out so we can see it; call execute('%segment_volumes_obc(sub_segment_temp=&sub_seg., volume=&vol.)'); end; run;
You need to mask the macro in the call execute, or all macro code will be executed immediately, while data or proc step code will wait until the current data step is finished:
call execute('%nrstr(%segment_volumes_obc(sub_segment_temp=&sub_seg., volume=&vol.))');
Hello,
It will be easier to answer if you show us the log.
I see the following line in your program :
call execute('%segment_volumes_obc(sub_segment_temp=&sub_seg., volume=&vol.)');
Since the argument string is enclosed in single quotes, the macrovariables in the string will not be resolved.
You have to use single quotes with call execute in order to avoid a premature execution of the macro. If you need to resolve
some macrovariables, build the argument string by a concatenation of substrings.
I don't think those suggestions are what's causing my problem. Here's the log:
1 The SAS System 08:47 Tuesday, January 30, 2018 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Selection_Filter_for_OBC_Volumes'; 4 %LET _CLIENTPROCESSFLOWNAME='Apply Communication Rules'; 5 %LET _CLIENTPROJECTPATH='/fs4/mktg_systems/04_selections_mart_area/Generic Selections Process.egp'; 6 %LET _CLIENTPROJECTNAME='Generic Selections Process.egp'; 7 %LET 7 ! _SASPROGRAMFILE='/fs4/mktg_systems/04_selections_mart_area/input_files/SEL_INPUTS/Selection_Filter_fo 7 ! r_OBC_Volumes.sas'; 8 9 ODS _ALL_ CLOSE; 10 OPTIONS DEV=ACTIVEX; 11 GOPTIONS XPIXELS=0 YPIXELS=0; 12 FILENAME EGSR TEMP; 13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 14 STYLE=HtmlBlue 15 STYLESHEET=(URL="file:///C:/ProgramData/App-V/0750226A-50CF-45FF-88C7-0A961A0274F6/ACF96B0A-48C4- 15 ! 4CCC-A320-B29A7EF2EE1E/Root/VFS/ProgramFilesX86/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 16 NOGTITLE 17 NOGFOOTNOTE 18 GPATH=&sasworklocation 19 ENCODING=UTF8 20 options(rolap="on") 21 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 22 23 GOPTIONS ACCESSIBLE; 24 %SYMDEL precount_1A precount_1B precount_1D precount_6B 25 count_1A count_1B count_1D count_6B 26 precount count/nowarn; 27 28 %macro segment_volumes_obc(sub_segment_temp=, volume=); 29 30 proc sql; 31 create table segment_records_&sub_segment_temp. as 32 select * 33 from sel_inpt.sub_segmentation 34 where sub_segment = "&sub_segment_temp." /* and comms_type = 'OBC'*/; 35 quit; 36 37 proc sql noprint; 38 select count(*) into :precount 39 from segment_records_&sub_segment_temp.; 40 quit; 41 42 %if &precount. < &volume. %then %do; 43 proc surveyselect data = segment_records_&sub_segment_temp. 44 method = srs n = &precount. out = segment_sample_&sub_segment_temp.; 45 run; 46 %end; 47 %else %do; 48 proc surveyselect data = segment_records_&sub_segment_temp. 49 method = srs n = &volume. out = segment_sample_&sub_segment_temp.; 50 run; 51 %end; 52 53 proc sql noprint; 54 select count(*) into :count 55 from segment_sample_&sub_segment_temp.; 56 quit; 57 58 %put "precount: ", &precount; 59 %put "count: ", &count; 60 %put "volume: ", &volume; 61 %let diff = %sysevalf(&volume - &count); 62 %put "diff: ", &diff; 2 The SAS System 08:47 Tuesday, January 30, 2018 63 64 %mend segment_volumes_obc; 65 66 %global diff; 67 %let diff = 0; 68 69 70 data temp; 71 set sel_inpt.obc_segment_priorities; 72 retain diff; 73 74 call symput('sub_seg', sub_segment); 75 diff = symget('diff'); * store the macro variable diff for later calculation; 76 77 if volume ^= . then do; 78 volume2 = sum(volume,diff); 79 call symput('vol',volume2); *use previously stored/retained value of diff for calculation; 80 vol = symget('vol'); *print it out so we can see it; 81 call execute('%segment_volumes_obc(sub_segment_temp=&sub_seg., volume=&vol.)'); 82 end; 83 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 78:24 NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 79:21 WARNING: Apparent symbolic reference PRECOUNT not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &precount. < &volume. ERROR: The macro SEGMENT_VOLUMES_OBC will stop executing. WARNING: Apparent symbolic reference PRECOUNT not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &precount. < &volume. ERROR: The macro SEGMENT_VOLUMES_OBC will stop executing. WARNING: Apparent symbolic reference PRECOUNT not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &precount. < &volume. ERROR: The macro SEGMENT_VOLUMES_OBC will stop executing. WARNING: Apparent symbolic reference PRECOUNT not resolved. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &precount. < &volume. ERROR: The macro SEGMENT_VOLUMES_OBC will stop executing. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 21 observations read from the data set SEL_INPT.OBC_SEGMENT_PRIORITIES. WARNING: The data set WORK.TEMP may be incomplete. When this step was stopped there were 21 observations and 6 variables. NOTE: Compressing data set WORK.TEMP increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. WARNING: Data set WORK.TEMP was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 1552.00k OS Memory 24212.00k Timestamp 30/01/2018 08:47:30 AM Step Count 459 Switch Count 124 Page Faults 0 Page Reclaims 391 Page Swaps 0 Voluntary Context Switches 460 Involuntary Context Switches 0 Block Input Operations 56 Block Output Operations 16 3 The SAS System 08:47 Tuesday, January 30, 2018 NOTE: CALL EXECUTE generated line. 1 + proc sql; 1 + create table segment_records_1A as select * from sel_inpt.sub_segmentation where sub_segment = "1A" ; NOTE: Compressing data set WORK.SEGMENT_RECORDS_1A increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: Table WORK.SEGMENT_RECORDS_1A created, with 15 rows and 4 columns. 1 + quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 6224.56k OS Memory 29092.00k Timestamp 30/01/2018 08:47:30 AM Step Count 460 Switch Count 34 Page Faults 0 Page Reclaims 413 Page Swaps 0 Voluntary Context Switches 82 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 1016 1 + proc sql noprint; select count(*) into :precount from segment_records_1A; quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 5947.03k OS Memory 28832.00k Timestamp 30/01/2018 08:47:30 AM Step Count 461 Switch Count 14 Page Faults 0 Page Reclaims 146 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 2 + proc sql; 2 + create table segment_records_1B as select * from sel_inpt.sub_segmentation where sub_segment = "1B" ; NOTE: Compressing data set WORK.SEGMENT_RECORDS_1B increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: Table WORK.SEGMENT_RECORDS_1B created, with 38 rows and 4 columns. 2 + quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 6222.87k OS Memory 29092.00k Timestamp 30/01/2018 08:47:30 AM Step Count 462 Switch Count 34 Page Faults 0 Page Reclaims 404 Page Swaps 0 4 The SAS System 08:47 Tuesday, January 30, 2018 Voluntary Context Switches 83 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 1024 2 + proc sql noprint; select count(*) into :precount from segment_records_1B; quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 5948.34k OS Memory 28832.00k Timestamp 30/01/2018 08:47:30 AM Step Count 463 Switch Count 14 Page Faults 0 Page Reclaims 143 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 3 + proc sql; 3 + create table segment_records_1D as select * from sel_inpt.sub_segmentation where sub_segment = "1D" ; NOTE: Compressing data set WORK.SEGMENT_RECORDS_1D increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: Table WORK.SEGMENT_RECORDS_1D created, with 106 rows and 4 columns. 3 + quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 6223.40k OS Memory 29092.00k Timestamp 30/01/2018 08:47:30 AM Step Count 464 Switch Count 34 Page Faults 0 Page Reclaims 400 Page Swaps 0 Voluntary Context Switches 81 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 1016 3 + proc sql noprint; select count(*) into :precount from segment_records_1D; quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 5948.06k OS Memory 28832.00k Timestamp 30/01/2018 08:47:30 AM Step Count 465 Switch Count 14 Page Faults 0 Page Reclaims 143 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 5 The SAS System 08:47 Tuesday, January 30, 2018 Block Input Operations 0 Block Output Operations 0 4 + proc sql; 4 + create table segment_records_6B as select * from sel_inpt.sub_segmentation where sub_segment = "6B" ; NOTE: Compressing data set WORK.SEGMENT_RECORDS_6B increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: Table WORK.SEGMENT_RECORDS_6B created, with 673 rows and 4 columns. 4 + quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 6222.87k OS Memory 29092.00k Timestamp 30/01/2018 08:47:30 AM Step Count 466 Switch Count 34 Page Faults 0 Page Reclaims 399 Page Swaps 0 Voluntary Context Switches 81 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 1016 4 + proc sql noprint; select count(*) into :precount from segment_records_6B; quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 5947.93k OS Memory 28832.00k Timestamp 30/01/2018 08:47:30 AM Step Count 467 Switch Count 14 Page Faults 0 Page Reclaims 143 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 84 85 GOPTIONS NOACCESSIBLE; 86 %LET _CLIENTTASKLABEL=; 87 %LET _CLIENTPROCESSFLOWNAME=; 88 %LET _CLIENTPROJECTPATH=; 89 %LET _CLIENTPROJECTNAME=; 90 %LET _SASPROGRAMFILE=; 91 92 ;*';*";*/;quit;run; 93 ODS _ALL_ CLOSE; 94 95 96 QUIT; RUN; 97
Exactly what I'm aiming at. You can see that the macro errors happen while the data step that does the call execute is still running, but the NOTEs from the data and proc sql steps come later.
You need to mask the macro in the call execute, or all macro code will be executed immediately, while data or proc step code will wait until the current data step is finished:
call execute('%nrstr(%segment_volumes_obc(sub_segment_temp=&sub_seg., volume=&vol.))');
Thanks! That's cleared up the errors! You're a saint!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.