How to dynamically change "%put list " in Macro depending on the dataset.

[SASuserlot]

Hi 

I have a requirement when I am creating a macro variables suing %total macro below. However , how I can  change the %put list in the macro dynamically( red font in code). 

For example in my 'x' data I have 4 observations  but in my next dataset 'Y' I have 6. So How I can change it dynamically depending on the dataset.  I appreciate your time and inputs. Thanks.

data x;
input drug count;
cards;
1 2
2 35
3 43
4 26
; 
run;

data y;
input drug count;
cards;
1 2
2 35
3 43
4 26
7 45
5 28
; 
run;
%macro total (in=);
data _null_;
     set &in.;
if count=. then count=0;
call symput ('drug'||trim(left(put(drug,best.))),trim(left(put(count,best.))));
run;

%put &drug1 &drug2 &drug3 &drug4 ;
%mend;

%total (in= x);
%total (in= y);

[PeterClemmensen]

Why not just put the values directly in the Data Step with the Put Statement?

[ballardw · Accepted solution]

Why?

A really don't see the utility here.

A somewhat different approach, assuming you really need those macro variables at all:

%macro total (in=);
data _null_;
     set &in.;
if count=. then count=0;
call symput ('drug'||trim(left(put(drug,best.))),trim(left(put(count,best.))));
run;

proc sql noprint;
   select drug into: druglist separated by ' '
   from &in.;
quit;

%put &druglist. ;
%mend;

The sql code creates a single macro variable that should have the contents of the drug macro variables and %put that variable.

[SASuserlot]

I do understand u r question . Its actually part of bigger macro where the datasets created running through the proc freq and other procedure. I just given the sample of dataset how it may look at the end before creating the macro variables. I may have to run the step before the macro creation for multiple times.. %put  will give me peace of mind when I look through the log to check my number matched for validation instead of opening the dataset.

[Quentin]

It looks like you're creating a bunch of local macro vars. Since you just want to write values to the log

%put _local_ ;

might be the easiest way to see it.

[SASuserlot]

 Thanks. This my log, Is I am doing any thing wrong!

[Quentin]

I'm guessing you might have created these macro variables as global macro variables earlier.  

Try:

%put _user_ ;

That will show all user-created macro variables (global and local). Generally, it's better to create local macro variables.

Another option, if you want to print the value of all macro variables that start with DRUG, is to use the dictionary table/view that stores values of macro variables.

data _null_;
  set sashelp.vmacro;
  where name like "DRUG%" ;  
  put name value ;
run;

[SASuserlot]

Thank you.

[SASuserlot]

Thank you for taking time. I use these macro variable some where down the road in tables creation for validation. So I would like to check them in the log instead of opening the dataset to compare my numbers. The code you mentioned is resolve  to drugs list, I can change that to count list. which  do my job. 

[Reeza]

data x;
input drug count;
cards;
1 2
2 35
3 43
4 26
; 
run;

data y;
input drug count;
cards;
1 2
2 35
3 43
4 26
7 45
5 28
; 
run;
%macro total (in=);
data _null_;
     set &in. end=eof;

call symputx (catt('drug', put(drug,best. -l)) , coalesce(count, 0), 'g');

if eof then call symputx('num_drugs', _n_);
run;

%do i=1 %to &num_drugs;
%put &&&drug&i.;
%end;

%mend;

%total (in= x);
%total (in= y);

Here's a slightly cleaned up version of your code, but for your Y data set your drugs are numbered non-sequentially, ie drug7, no drug6 but 6 drugs. Do you want to be able to access those values dynamically?

If so, perhaps use two macro variables, one to contain variable name and one value but index with rows?

%macro total (in=);
data _null_;
     set &in. end=eof;

*drug name;
call symputx(catt('drug_name', _n_), drug, 'g');

*drug count;
call symputx(catt('drug_count', _n_) , coalesce(count, 0), 'g');

if eof then call symputx('num_drugs', _n_);
run;

%do i=1 %to &num_drugs;
%put Drug Name : &&&drug_name&i.;
%put Drug Count: &&&drug_count&i.;
%end;

%mend;

%total (in= x);
%total (in= y);

However, I suspect this is all possibly a bad design and CALL EXECUTE and data driven approach may be better. 

---

@SASuserlot wrote:

Hi 

I have a requirement when I am creating a macro variables suing %total macro below. However , how I can  change the %put list in the macro dynamically( red font in code). 

For example in my 'x' data I have 4 observations  but in my next dataset 'Y' I have 6. So How I can change it dynamically depending on the dataset.  I appreciate your time and inputs. Thanks.

data x;
input drug count;
cards;
1 2
2 35
3 43
4 26
; 
run;

data y;
input drug count;
cards;
1 2
2 35
3 43
4 26
7 45
5 28
; 
run;
%macro total (in=);
data _null_;
     set &in.;
if count=. then count=0;
call symput ('drug'||trim(left(put(drug,best.))),trim(left(put(count,best.))));
run;

%put &drug1 &drug2 &drug3 &drug4 ;
%mend;

%total (in= x);
%total (in= y);

---

[SASuserlot]

Thanks for taking time. Please see my responses for your question in blue.

Here's a slightly cleaned up version of your code, but for your Y data set your drugs are numbered non-sequentially, ie drug7, no drug6 but 6 drugs. Do you want to be able to access those values dynamically?

Ans: Yes, the number can be any thing  for drug, I just given in sequentially for sample purpose in X and skipped in Y. So I believe , first approach will have the problem because of  _n_. thanks again for taking your time.

[Reeza]

So far, @ballardw solution and my other solution would allow you to deal with issue correctly and allow you to loop through. Otherwise if you're planning to use those variables later how will you know to skip the 6th index?

---

@SASuserlot wrote:

Thanks for taking time. Please see my responses for your question in blue.

Here's a slightly cleaned up version of your code, but for your Y data set your drugs are numbered non-sequentially, ie drug7, no drug6 but 6 drugs. Do you want to be able to access those values dynamically?

Ans: Yes, the number can be any thing  for drug, I just given in sequentially for sample purpose in X and skipped in Y. So I believe , first approach will have the problem because of  _n_. thanks again for taking your time.

---

[SASuserlot]

May be I did not given the example good enough, sorry for that. When you sending input dataset we usually have the idea what is drug names or drug numbers and any missing. the 'count ' given in example is the number came after proc freq. like out of 100 how many took drug 1 and how many drug 2.... etc. so I was indirectly looking for that. we sort data by number so that we know the order.