topic Re: Create a macro list counting values in a macro variable in SAS Procedures

Create a macro list counting values in a macro variable

windy — Tue, 17 Jan 2023 05:36:17 GMT

Hi all,

I have a macro variable containing a list of values.

%let listA = -5 -4 -3 -2 -1 0 1 2 3 4 5 6 7 8 9 10 (16 values)

Now I want to create macro variables called count and count_l.

Count returns the count of each item in the listA.

Count = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Count_l returns the count of each item in the listA if values are smaller than or equal to 0.

Count_l = 1 2 3 4 5 6

Thank you so much in advance for any help.

Re: Create a macro list counting values in a macro variable

Kurt_Bremser — Tue, 17 Jan 2023 07:31:24 GMT

What will you be doing with the resulting macro variables?

Re: Create a macro list counting values in a macro variable

windy — Tue, 17 Jan 2023 09:47:41 GMT

Hi @Kurt_Bremser

For count, I will create dummy variables which names are related to the count (like A_1, A_2,..., A_16) after each iteration.

For count_l, I just store the total number of items satisfying the condition.

Re: Create a macro list counting values in a macro variable

PaigeMiller — Tue, 17 Jan 2023 11:15:41 GMT

@windy wrote:

For count, I will create dummy variables which names are related to the count (like A_1, A_2,..., A_16) after each iteration.

For count_l, I just store the total number of items satisfying the condition.

You don't need dummy variables to obtain counts and store results. You can use PROC FREQ. You don't need macro variables either. Could you please provide even more details about what you are doing, describe the goal and the end result. It seems you are choosing a very difficult approach that simply isn't necessary.

Re: Create a macro list counting values in a macro variable

Ksharp — Tue, 17 Jan 2023 13:10:43 GMT

%let listA = -5 -4 -3 -2 -1 0 1 2 3 4 5 6 7 8 9 10 ;

data temp;
 do i=1 to countw("&listA."," ");
   value=input(scan("&listA.",i," "),best.);
   output;
 end;
run;
proc sort data=temp;by value;run;
data temp;
 set temp;
 count+1;
 if value<=0 then count_i+1;
  else count_i=.;
run;
options missing=' ';
proc sql noprint;
select count into :count separated by ' ' from temp;
select count_i into :count_i separated by ' ' from temp;
quit;

%put &=count. ;
%put &=count_i.;

Re: Create a macro list counting values in a macro variable

Astounding — Tue, 17 Jan 2023 13:41:35 GMT

As others have mentioned, this looks unusual as the objective of a SAS program. My personal suspicion is that this is a Python application being migrated to SAS. And SAS would have other, simpler ways to get to the final result (if we knew what the final result should be). At any rate, what you ask for is not difficult:

%macro counts (listA=);
   %local _i_ _k_;
   %global neg_list complete_list;
   %let neg_list=;
   %let complete_list=;
   %do _i_ = 1 %to %sysfunc(countw(&listA));
      %let complete_list = &complete_list &_i_;
      %if %sysevalf(%scan(&complete_list, &_i_) <= 0) %then %do;
         %let _k_ = &_k_ %eval(&_k_ +1);
         %let neg_list = &neg_list &_k_;
      %end;
   %end;
%mend counts;

Then call the macro using:

%counts (list=&listA)

The code is untested so might need a little debugging. And it contains a few subtleties such as using %SYSEVALF instead of %EVAL in case one of the numbers in the list contains a decimal point. (Also the +1 when no space between the plus and the 1 avoids the need to initialize _k_.

But the bottom line is still what others have suggested. If we know the ultimate objective, we can guide you toward a more direct SAS-based approach.

Re: Create a macro list counting values in a macro variable

Tom — Tue, 17 Jan 2023 14:10:05 GMT

Count returns the count of each item in the listA.

Count = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

The meaning of the English sentence does not match the value desired.

The count of the number of items in the list is just 16.

%let count=%sysfunc(countw(&lista,%str( )));

If for some reason you want to convert that to the string you want just add a %DO loop.

Perhaps you meant to say you want the make a list of the INDEXes of the items in the list instead of "count" of the items?

That would more properly match the second list you requested.

%do index=1 %to %sysfunc(countw(&listA,%str( )));
  %let count=&count &index;
  %if %scan(&listA,&index,%str( )) <= 0 %then %let Count_l=&Count_l &index;
%end;

If the values are also going to be ordered, like in your example, then perhaps the second could also just be reduced to a single number, the index to where the zero value appears, instead? That can be found by using the E modifier with the FINDW() function.

%let Count_l=%sysfunc(findw(&lista,0,,se));

Example:

13   %let listA = -5 -4 -3 -2 -1 0 1 2 3 4 5 6 7 8 9 10 ;
14   %let count=%sysfunc(countw(&listA,%str( )));
15   %let Count_l=%sysfunc(findw(&listA,0,,se));
16   %put &=count &=Count_l ;
COUNT=16 COUNT_L=6

Re: Create a macro list counting values in a macro variable

windy — Tue, 17 Jan 2023 15:14:55 GMT

Thank you so much @Tom. This code fits well to the macros that I am working on. Thanks for correcting me. What I need is actually the indexes of the items.

Re: Create a macro list counting values in a macro variable

windy — Tue, 17 Jan 2023 15:19:27 GMT

You're right that I am trying to translate the codes from other language to SAS. My SAS skills related to macro is not strong, so I am pretty baffled when it comes to deal with them. Thanks so much for your help. I really appreciate.

Re: Create a macro list counting values in a macro variable

windy — Tue, 17 Jan 2023 15:28:54 GMT

Thanks so much for your help. The codes worked very well, and I got the expected results. As I am writing a bigger macro, I chose the other option as the solution. But I learn a lot from your codes. I very appreciate it.

Re: Create a macro list counting values in a macro variable

windy — Tue, 17 Jan 2023 15:36:20 GMT

Sorry for making a confusing request. I realized that I had overcomplicated the problems. It's because I am trying to translate from other languages to SAS. I'll be more straightforward when I make a request next time. Thank you so much for your comment. I also appreciate all the helps I got from the SAS community.