Proc SQL into: is not updating my macro

[EnchantedEggs]

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;

[gamotte]

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.

[gamotte]

Also, the macrovariables created by call symput are not readily available. They can only be used after the data step has finished executong.

[EnchantedEggs]

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         

[Kurt_Bremser]

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.

[Kurt_Bremser · Accepted solution]

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.))');

[EnchantedEggs]

Thanks! That's cleared up the errors! You're a saint!