How can I use a proc procedure step in proc DS2 to call a proc procedure step in a macro

[_Sas_Beginner_]

How can I use a proc procedure step in proc DS2 to call a proc procedure step in a macro， as if it were a call execute statement in Data step

[SASKiwi]

You are describing what you want as a solution to a problem without explaining what the problem is. It would help if you explained this as well as there may be a better way to solve it. 

[_Sas_Beginner_]

Thank you for your reply，I mean, how to call "%Macro~%Mend" in proc DS2

[Tom]

---

@_Sas_Beginner_ wrote:
Thank you for your reply，I mean, how to call "%Macro~%Mend" in proc DS2

---

The macro processor is a PRE processor.  Embedding a macro definition into the middle of a data step (whether a real one or the DS2 one) makes no sense.

[_Sas_Beginner_]

I found that the data step can use "call execute" to directly call "macro-mend", or you can directly wrap "macro-mend" with the "run_macro" of the fcmp function to complete the analysis of another thread and directly substitute into the data step, but the above can only be fully implemented in the data step, and the ds2 step cannot call the macro, which greatly improves the length of the code and reduces work efficiency

/*The following code can only be implemented in the data step, but not in ds2
*/
%macro distinct_values;

%let input_table = %sysfunc(dequote(&input_table));

%let column = %sysfunc(dequote(&column));

%let output_table = %sysfunc(dequote(&output_table));

proc sql;

create table &output_table as

select distinct &column

from &input_table;

%mend;
proc fcmp outlib=work.funcs.sql;

function get_distinct_values(input_table $, column $, output_table $);

rc = run_macro('distinct_values', input_table, column, output_table);

return (rc);

endsub;

run;
options cmplib = work.funcs;

data _null_;

rc = get_distinct_values('sashelp.shoes', 'region', 'work.regions');

id=open('work.regions');

if id then nobs=attrn(id,'NOBS');

put nobs;

run;

[AhmedAl_Attar · Accepted solution]

Hi @_Sas_Beginner_ 

Very quickly,  for this example "Getting the number of unique values of a specific column" You don't need to over complicate it, here is a simpler approach

This may not answer your Proc DS2 question/issue, but I wanted to show you simpler way for your example

/* For confirmation */
proc sql;
	select distinct region from sashelp.shoes;
quit;

/* Does everything in a single step !*/
data _null_;
	rc=dosubl('%let g_unique=; proc sql noprint; select count (distinct region) into :g_unique from sashelp.shoes; quit;');
	nobs=symget('g_unique');
	put nobs=;
run;

Hope this helps

[_Sas_Beginner_]

THANKS FOR YOUR ANSWER!

[AhmedAl_Attar]

Hi @_Sas_Beginner_ 

Thank you accepting my answer, but here is a Proc DS2 approach if you still needed. This will highly depend on the ability to fit the data into memory

/* DS2 cannot work with concatenated libraries, such as SASHELP! */
proc copy in=sashelp out=work;
select shoes;
run;

proc ds2 ;
	data _null_;
		dcl package hash h();
		dcl char(55) region;
		dcl int cnt;
		method init();
			h.dataset('work.shoes');
			h.keys([region]);
			h.hashexp(8);
			h.ordered('a');
			h.multidata('no');
			h.defineDone();
			
			/* Get number of unique values */
			cnt=h.num_items;
			put cnt=;
		end;
	enddata;
run;
quit;

Hope this helps

[AhmedAl_Attar]

@yabwon
This is the Proc DS2 approach 😉
it takes around 0.07 seconds for the region column from the sashelp.shoes data set

[yabwon]

On my machine even faster:

NOTE: PROCEDURE DS2 used (Total process time):
      real time           0.02 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
      memory              8071.31k

but  a good old data step is still faster (and memory cheaper) 😉 😉 😉

1    data _null_;
2        dcl hash h(dataset:'work.shoes2');
3          h.defineKey("region");
4          h.defineDone();
5
6          /* Get number of unique values */
7          cnt=h.num_items;
8          put cnt=;
9        stop;
10       set work.shoes;
11   run;

NOTE: There were 395 observations read from the data set WORK.SHOES2.
cnt=10
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              1099.40k

All the best

Bart

[yabwon]

DoSubL() is not supported in DS2 😞

It also not support Resolve()...

1    %let x = 17;
2
3    proc ds2;
4
5    data _null_;
6
7    method init();
8    rc = resolve('&x.');
9    end;
10
11   enddata;
12   run;
ERROR: Compilation error.
ERROR: Unknown function resolve.
13
14   quit;

Bart

[AhmedAl_Attar]

Hi @yabwon 

I did mention the dosubl was not for the Proc DS2 question/issue. For DS2 it would best to use either SQLSTMT package or the HASH Package

[yabwon]

My fault, didn't noticed that. Sorry.

[_Sas_Beginner_]

Especially in the face of large-scale projects, it is necessary to use proc DS2, macro, and FCMP cross-use

[LinusH]

I'm not sure I understand the use case is for your example, why not call the macro directly from open code?

You could add put NOBS to the macro.

Generally speaking, if you wish to call sub-routines from PROC DS2, use methods.