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
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_ 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.
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;
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
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
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
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
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
My fault, didn't noticed that. Sorry.
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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: