topic How to repeat a symput function to multiple observations in SAS Data Management

How to repeat a symput function to multiple observations

TLSAS — Wed, 30 May 2018 17:36:36 GMT

Hello,

I want to require 6 variables (in a wide data format) to be equal to a certain value, pending on a variable's "values". TO make it simple, I created only 2 variables here as an example. Depend on the value in each row of variable "mon", I want to use that value for the suffix "X" of the variable "monthX", and require that "monthX" to be equal "c". Here is my code, and it seem like it only works for the first row. How do I make this work for multiple rows:

data a;
   input id mon $2. month1 $2. month2 $2. ;
   datalines;
1 2 c c
2 1 c n
3 1 n n
4 2 c n
;
run;
proc print data=a; run;

%macro test(dataset);
data _null_;
   set &dataset. nobs=x;
    call symputx('max',x);
run;
data b;
   set &dataset.;
%do i=1 %to &max.;
   call symputx('mon1',"month"||compress(mon));
   if &mon1. in('c') then enroll=1;
%end;
%mend test;
%test(a);
proc print data=b; run;

And here is the output for the dataset b. The "enroll" variable in row 2, and 4 supposed to be 1 as well.

Obs id mon month1 month2 enroll1234

1	2	c	c	1
2	1	c	n	.
3	1	n	n	.
4	2	c	n	.

Re: How to repeat a symput function to multiple observations

novinosrin — Wed, 30 May 2018 17:50:46 GMT

I'm afraid you do not need a macro wrapper nor

set &dataset. nobs=x;
call symputx('max',x);

%do i=1 %to &max.;
call symputx('mon1',"month"||compress(mon));
if &mon1. in('c') then enroll=1;
%end;

Macro language in full is completely unnecessary and won't work well for your need.

Can you tell us what you want in your Dataset b(your output dataset)? Please

Re: How to repeat a symput function to multiple observations

novinosrin — Wed, 30 May 2018 17:56:56 GMT

@TLSAS Are you after this?

data a;
    input id mon $2. month1 $2. month2 $2. ;
    datalines;
1 2 c c
2 1 c n
3 1 n n
4 2 c n
;
run;


data b;
set a;
array t(*) month:;
do _n_=1 to dim(t);
if t(_n_)='c' then enroll=1;
end;
run;

Re: How to repeat a symput function to multiple observations

TLSAS — Wed, 30 May 2018 17:57:37 GMT

Hi Novinosrin,

Thank you for your reply.

I used the macro because I always got the error message with the %do statement (see bellow).

For output dataset b, I want to create a binary variable that equal to 1 whenever the variable "mon" has the value that equal to the suffix of the variable "month1-month2".

Is that clear to you?

ERROR: The %DO statement is not valid in open code.
240
241 %do i=1 %to &max.;
242      call symputx('mon1',"month"||compress(mon));
         ----
         180

Re: How to repeat a symput function to multiple observations

TLSAS — Wed, 30 May 2018 18:00:51 GMT

Thanks for the code, but this is not what I want.

I want to create a binary variable that equal to 1 whenever the variable "mon" has the value that equal to the suffix of one of the variables "month1-month2".

Re: How to repeat a symput function to multiple observations

novinosrin — Wed, 30 May 2018 18:08:28 GMT

@TLSAS wrote:
Thanks for the code, but this is not what I want.

I want to create a binary variable that equal to 1 whenever the variable "mon" has the value that equal to the suffix of one of the variables "month1-month2".

With that all 4 in your sample does match

data a;
    input id mon $2. month1 $2. month2 $2. ;
    datalines;
1 2 c c
2 1 c n
3 1 n n
4 2 c n
;
run;
data b;
set a;
array t(*) month:;
do _n_=1 to dim(t);
if mon=char(vname(t(_n_)),length(vname(t(_n_)))) then enroll=1;
end;
run;

Re: How to repeat a symput function to multiple observations

TLSAS — Wed, 30 May 2018 18:28:31 GMT

@novinosrin

Thank you so much! This works very well.

I forgot to mention that because I wanted the enroll=1 whenever the "mon" value is aqual to the suffix of one of the variable "month1=month2" PLUS the "monthX" variable and Month(X+1) variable have to be equal to "c".

For example, if mon=1 & month1='c' & month2='c' then enroll=1;

However, I modified your code and it did not work (see the error). Could you please help!

data a;
input id mon $2. month1 $2. month2 $2. ;
datalines;
1 2 c c
2 1 c c
3 1 n n
4 2 c n
;
run;
data b;
set a;
array t(*) month:;
do _n_=1 to dim(t);
if mon=char(vname(t(_n_)),length(vname(t(_n_)))) and t(_n_) ='c' and t(_n_+1) ='c' then enroll2=1;
end;
run;

ERROR: Array subscript out of range at line 409 column 70.
id=1 mon=2 month1=c month2=c enroll2=. _ERROR_=1 _N_=2
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.A.
WARNING: The data set WORK.B may be incomplete. When this step was stopped there were 0
         observations and 5 variables.
WARNING: Data set WORK.B was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: How to repeat a symput function to multiple observations

novinosrin — Wed, 30 May 2018 18:32:13 GMT

Do you mean both month1 and month2 will have to be C or any one of the two can be C?

Re: How to repeat a symput function to multiple observations

TLSAS — Wed, 30 May 2018 18:36:04 GMT

@novinosrin

This is an example dataset, so it is much more simple than my dataset. I have maybe 50 variables of month1-month70 that play the same role as "month1-month2" here.

So I want whenever the "mon" variable is equal to the suffix of a monthX variable, that monthX plus the month follow monthX (i.e

MOnthX+1) have to both be equal to "c" as well.

Thanks,

Re: How to repeat a symput function to multiple observations

novinosrin — Wed, 30 May 2018 19:12:04 GMT

@TLSAS I certainly do understand your real data will have month vars

if "mon" variable is equal to the suffix of a monthX variable ---if this condition is true

1 2 c c

2 = month2

so monthx=month2

month2=c can't be compared with month3=c. this case happens dealing with the last variable in the list. What's your take on that?

Therefore in your sample

1 2 c c
2 1 c n
3 1 n n
4 2 c n

no records will satisfy monthx= monthx+1 after establishing "mon" variable is equal to the suffix of a monthX variable -

Re: How to repeat a symput function to multiple observations

TLSAS — Wed, 30 May 2018 19:37:27 GMT

@novinosrin

That is correct. The dataset has no record that satisfies the requirement. So I changed one row in the input dataset .

However, my modified code is not working because there is always a last variable that is out of the range specified in the array statement.

Any suggestion what should I do?

data a;
input id mon $2. month1 $2. month2 $2. ;
datalines;
1 2 c c
2 1 c c
3 1 n n
4 2 c n
;
run;

Re: How to repeat a symput function to multiple observations

novinosrin — Wed, 30 May 2018 19:43:58 GMT

Please test the below

data a;
    input id mon $2. month1 $2. month2 $2. ;
    datalines;
1 2 c c
2 1 c c
3 1 n n
4 2 c n
;
run;
data b;
set a;
array t(*) month:;
do _n_=1 to dim(t)-1;
if mon=char(vname(t(_n_)),length(vname(t(_n_)))) and t(_n_)='c' and t(_n_+1)='c' then enroll=1;
end;
run;

and let me know

Re: How to repeat a symput function to multiple observations

TLSAS — Wed, 30 May 2018 19:48:35 GMT

This works perfectly! Thanks so much!

Best,

Re: How to repeat a symput function to multiple observations

novinosrin — Wed, 30 May 2018 19:50:07 GMT

@TLSAS Always welcome and feel free. Take care

Re: How to repeat a symput function to multiple observations

AnnaBrown — Thu, 31 May 2018 13:42:20 GMT

Hi @TLSAS,

I'm glad you found some useful info! If one of @novinosrin's replies was the exact solution to your problem, can you "Accept it as a solution"? Or if one was particularly helpful, feel free to "Like" it. This will help other community members who may run into the same issue know what worked.

Thanks!
Anna

Re: How to repeat a symput function to multiple observations

TLSAS — Thu, 31 May 2018 13:59:56 GMT

@AnnaBrown

I marked the solution!

Thanks,

Re: How to repeat a symput function to multiple observations

TLSAS — Thu, 31 May 2018 22:10:57 GMT

Hi @novinosrin,

I expand the codes yesterday into the dataset with multiple "month" variables, when months suffix is more than 10 (i.e the suffix is 2 numbers) then the code is not correct anymore. See the code below, its not working. Could you help?

data a;
input id mon $2. month11 $2. month12 $2. month13 $2. month14 $2. month15 $2. month16 $2.;
datalines;
1 12 c c c c c n
2 10 c c n n c n
3 13 n n c c n n
4 12 c n c n c c
;
run;

data b;
set a;
array t(*) month:;
do _n_=1 to dim(t)-1;
if mon=char(vname(t(_n_)),length(vname(t(_n_)))) and t(_n_)='c' and t(_n_+1)='c' then enroll=1;
end;
run;

Re: How to repeat a symput function to multiple observations

novinosrin — Thu, 31 May 2018 22:12:54 GMT

@TLSAS Aah ok, ofcourse won;t work for the reason length(vname(t(_n_)) finds the last one.. Brb soon

Re: How to repeat a symput function to multiple observations

novinosrin — Thu, 31 May 2018 22:34:58 GMT

@TLSAS Please test the below and let me know. I am here at my college lab for two more hours to make any further changes if needed:

data a;
    input id mon $2. month11 $2. month12 $2. month13 $2. month14 $2. month15 $2. month16 $2.;
    datalines;
1 12 c c c c c n
2 10 c c n n c n
3 13 n n c c n n
4 12 c n c n c c
;
run;

data b;
set a;
array t(*) month:;
do _n_=1 to dim(t)-1;
if mon=substr(vname(t(_n_)),anydigit(vname(t(_n_))))  and t(_n_)='c' and t(_n_+1)='c' then enroll=1;
end;
run;

Re: How to repeat a symput function to multiple observations

TLSAS — Fri, 01 Jun 2018 03:23:15 GMT

Thanks @novinosrin! This works well.

I was trying to use substr but it didn't work, and you always know what works. Amazing. Thanks!