How to read the variables in the %var statement one by one

[sahoositaram555]

Hi all,

I have a set of categorical variables (a b c d e) in the Inputdataset as defined in the %let statement and macro a .

I want to pass the categorical variables one by one in to the a macro the outputs in one data step.So far, i've tried my best to do as written below. kindly, suggest how to proceed ahead. 

%let variables=a b c d e ;

%macro a(out= ); 

%freq(DATASET = Inputdataset ,TRT=trt, TRTFMT =_NONE_ ,BYVAR = _NONE_, VAR=&variables, LABEL= ,FMTVAR= _NONE_, OUTDS= &out);

%mend a;

%a(out= );

[Jagadishkatam]

Could you please try the below untested macro, I updated the a macro as well by replacing the VAR=&variable with VAR=&out

%macro a(out= ); 
%freq(DATASET = Inputdataset ,TRT=trt, TRTFMT =_NONE_ ,BYVAR = _NONE_, VAR=&out, LABEL= ,FMTVAR= _NONE_, OUTDS= &out);
%mend a;

%macro test;
%let variables=a b c d e ;
%let cnt=%sysfunc(countw(&variables,' '));
%do i = 1 %to &cnt;
%a(out=%unquote(%scan(&variables,&i,' ')));
%end;
%mend;

%test

[sahoositaram555]

Hi @Jagadish,
I've tried executing your code , it didn't work. how ever i would like to understand reason behind your approach in using few functions. Kindly, help me with your response.
1. use/advantage of countw() inside the test macro.
it's not getting resolved since i tried
executing only few parts of your code:
%macro test;
%let variables=a b c d e;
%let cnt=%sysfunc(countw(&variables,' '));
%mend test;
%put &cnt;
and result was :
WARNING: Apparent symbolic reference CNT not resolved.

[Jagadishkatam]

Please try the below code to resolve the cnt macro variable. Here countw function will return the number if words or letters separated by delimiter.

Since the countw function is designed to accept the variables, and we are passing the macro variable we need to use the %sysfunc with countw function.

%macro test;
%global cnt;
%let variables=a b c d e;
%let cnt=%sysfunc(countw(&variables,' '));

%mend test;

%test
%put &cnt;

[Tom]

---

@sahoositaram555 wrote:
Hi @Jagadish,
I've tried executing your code , it didn't work. how ever i would like to understand reason behind your approach in using few functions. Kindly, help me with your response.
1. use/advantage of countw() inside the test macro.
it's not getting resolved since i tried
executing only few parts of your code:
%macro test;
%let variables=a b c d e;
%let cnt=%sysfunc(countw(&variables,' '));
%mend test;
%put &cnt;
and result was :
WARNING: Apparent symbolic reference CNT not resolved.

---

That test doesn't make any sense.  The CNT macro variable was being created to find out how many values there were. If you wanted to test that logic then just submit a couple of lines of open code to see if you understand it.  (Note the code is using both spaces and single quotes as the word delimiters. That might work but will could cause trouble if your list of names included name literals like 'max salary'n.)

%let varlist=age sex height ;
%put Number of Variables = %sysfunc(countw(&varlist,%str( ),q));

The main point of the code you were responding to was the %DO loop, not the method for finding the upper bound on the number of iterations of the loop. Loop over the list and make one call to your existing macro with the next name from the list.

%do varnum=1 %to %sysfunc(countw(&varlist,%str( ),q));
  %let varname=%scan(&varlist,&varnum,%str( ),q);
  %freq(.... VAR=&varname .... );
%end;

[sahoositaram555]

Hi @Tom,
Your code looks quite convincing.I can see it has taken care everything,except 1 parameter OUTDS= option which I would request you to look upon. OUTDS= &out says all the variables eg: age sex height will have different output which further can be taken in to proc report as per the requirement. kindly refer to below written chunk of code:
%freq(DATASET = Inputdataset , VAR=&variables, ,OUTDS= &out);

Any additional inputs how to handle that?

I have thought of doing something like this, kindly suggest if I'm going in a right direction

%macro test1/parmbuff;
%global cnt;
%do i=1 %to %sysfunc(countw(&syspbuff,%str( ),q));
%let make=%scan(&syspbuff,&i,%str( ),q);
%let out=&make&i;
data y&i.;
%freq(DATASET = b2,TRT=trt, ,BYVAR = _NONE_,VAR=&make.,LABEL= ,
FMTVAR= _NONE_,COND= _NONE_,PCTTYPE= COL,P= _NONE_,DENOMNODISP = _NO_,
delALLDS= _YES_,MAXnumLEN= 6,clopper_pearson = _NONE_,OUTDS= &out.);
run;
%end;
%mend;
%let varlist=age sex height ;


or do you suggest to write something like
%let out=sex_output age_output height_output;
if yes, to this then, i need your help in where to use this in code?


Cheers,
Ram

[Tom]

Depends on what you want to do and also somewhat on what type of data your macro creates.

If the data it creates has the same structure for every variable being analyzed the it is probably most efficient to just combine them together.

%do ....
   ...
   %freq(... outds=this_one ... );
    proc append base=&out data=this_one force run;
%end;

If the structure changes based on what variable you analyze then you will require  a unique name for each variable. Either by using the number used in the loop as a suffix on some base name.  You could possibly build the name from the variable name but watch out if your variable names are long. Both dataset names and variable names are limited to the same 32 byte length.  Just using the variable names as the names of the datasets could work, but then you run the risk of conflicting with other datasets that are already using those names.

[sahoositaram555]

Hi @Tom ,

Many thanks for your quick responses to all my queries. I have somehow got the output by taking reference from your code that needs one step improvisation, which i would request you to have a look & comment.

%macro test1(s);
   %do i=1 %to %sysfunc(countw(&s,%str( ),q));
   %let varname =%scan(&s,&i.);
   %freq(DATASET = b2,TRT=trt, TRTFMT =_NONE_ ,BYVAR = _NONE_,VAR=&varname.,OUTDS=d_&varname.);
   %end;
%put &varname;
%mend test1;

%test1(sex);
%test1(response);
%test1(disease_status);
%test1(diabetes_status);        

when i execute the above code the for every categorical variables(eg: sex response disease_status etc.) especially when calling test1 macro for individually for each variable, I'm getting individual outputs named subsequently d_sex,d_response,d_disease_status  as i have mentioned in my code OUTDS=d_&varname. . Which is perfectly fine. But, the point is when i'm calling the macro like %test1(sex response disease_status diabetes_status) then it throws ambiguous errors.Is there a way out to fix this? I would need your help in improvising the code in a way that automatically all the outputs should get stored in one dataset at the moment it comes out of the loop by mentioning all the variables in a single macro call since i have more than 50 variables to pass on to the macro.

I have tried using proc append as you have  suggested but, it's not working ,seems like loop is not keeping the 1st variable's output after running through the second one, by this logic when i tried to store the output in to a dataset (this_one)as you have mentioned in your last reply, it only stores the last variables's output. Hope my query is clear to you. 

looking forward to get some ideas/help from you soon .

Cheers,

Ram 

[Tom]

You could replace the %FREQ() calls with %PUT statement to see if your looping code is working right.

   %do i=1 %to %sysfunc(countw(&s,%str( ),q));
   %let varname =%scan(&s,&i.);
   %put   freq(DATASET = b2,TRT=trt, TRTFMT =_NONE_ ,BYVAR = _NONE_,VAR=&varname.,OUTDS=d_&varname.);
   %end;

The issue might be that the %FREQ() macro is modifying your macro variables. You could try seeing what values they have after %FREQ() ends.

   %do i=1 %to %sysfunc(countw(&s,%str( ),q));
   %let varname =%scan(&s,&i.);
   %freq(DATASET = b2,TRT=trt, TRTFMT =_NONE_ ,BYVAR = _NONE_,VAR=&varname.,OUTDS=d_&varname.);
   %put AFTER FREQ &=i &=varname &=s ;
   %end;

[sahoositaram555]

Hi @Tom,

After executing the below command for 1 variable(EG: only sex ) by giving %test1(sex)  I'm getting i=sex s=sex  varname=sex. 

%put AFTER FREQ &=i &=varname &=s ;

 However when i execute the command %test1(sex aa bb cc xx yy) the nothing is printing in to the log file and the code is imply running with out stopping. 

So,after trying  few options I have at least figured out that the problem starts after execution of ist variable (in this case it is Sex.) after that the do loop is not taking the second variable as mentioned in the example(aa). so, can you suggest something on this how to go about fixing this? Is there a way in capturing the output once it executes with the sex variable and get stored in a output then the loop goes back again to get the second variable, kindly let me know, it would be a great help. 

Cheers,

Ram

[Tom]

I should have been an integer 1,2,3, etc. So the FREQ macro must be modifying the macro variable I. If you can modify that macro then add a %LOCAL statement to the top and have it make I (and any other variables it is modifying) be local. Or change the macro variable name used for the new %DO loop from I to something that the FREQ macro does not modify.

[PaigeMiller]

But, the point is when i'm calling the macro like %test1(sex response disease_status diabetes_status)

The macro is not written to accept multiple variables in its argument. It is written to have one variable (and not more than one) in the argument.

If you want it to work for multiple variables, then you have to modify the macro to include a loop inside the macro, very similar to the one I provided earlier, except that %freq is inside the loop, rather than what I did was to have the loop provide multiple lines in PROC FREQ.

Of course, if you have to use the %FREQ macro, then this requirement is slowing you down; the code I provided above using PROC FREQ does work.

[Jagadishkatam]

Please try the below code, tested with sashelp.class dataset

%macro freq(DATASET =,VAR=, OUTDS=  );
proc freq data=&DATASET;
table &var / out=&out;
run;
%mend;

%macro a(out= ); 
%freq(DATASET=sashelp.class ,VAR= &out, OUTDS=&out);
%mend a;

%macro test;
%let variables=name sex ;
%let cnt=%sysfunc(countw(&variables,' '));
%put &cnt;
%do i = 1 %to &cnt;
%a(out=%unquote(%scan(&variables,&i," ")));
%end;
%mend;

%test

[PaigeMiller]

What does %FREQ do?

Is it simply designed to run PROC FREQ on a variable and save the results in a SAS data set?

%let variables=a b c d e ;

%macro dothis; 
proc freq data=inputdataset;
    %do i=1 %to %sysfunc(countw(&variables));
        %let thisvar=%scan(&variables,&i,%str( ));
        table &thisvar/out=&thisvar._results;
    %end;
run;
%mend dothis;
%dothis

[sahoositaram555]

Hi @PaigeMiller,

U guessed almost right. %FREQ is a project specific macro to take care of steps from getting a few things done including frequency and further restructuring.However, here my concern involves how to read categorical variables one by one(eg: lets say: sex race disease_status etc.) and i need to use those variables one by one to pass in to %freq(predesigned macro) to finally get different outputs and later combining them (pretty much like a set command to stack up output datasets one upon another. I know, that this thing can be done by using proc freq macro that you have mentioned but i want to use specifically the study designed macro out of compulsion.
Hope you understand and suggest something on this.

Cheers,
Ram