SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to repeat a symput function to multiple observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

How to repeat a symput function to multiple observations

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
12cc1
21cn.
31nn.
42cn

.


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

[ Edited ]

@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;

 

View solution in original post


All Replies
PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

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

 

 

PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

Posted in reply to novinosrin

@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;
Occasional Contributor
Posts: 10

Re: How to repeat a symput function to multiple observations

Posted in reply to novinosrin

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".

Solution
3 weeks ago
PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

[ Edited ]

@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;

 

Occasional Contributor
Posts: 10

Re: How to repeat a symput function to multiple observations

Posted in reply to novinosrin

@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


 

 

 

 

 

 

PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

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

Occasional Contributor
Posts: 10

Re: How to repeat a symput function to multiple observations

Posted in reply to novinosrin

@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,

 

PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

[ Edited ]

@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 -

Occasional Contributor
Posts: 10

Re: How to repeat a symput function to multiple observations

Posted in reply to novinosrin

@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;

 

 

PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

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

 

 

Occasional Contributor
Posts: 10

Re: How to repeat a symput function to multiple observations

Posted in reply to novinosrin
This works perfectly! Thanks so much!

Best,
PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

@TLSAS Always welcome and feel free. Take care

Occasional Contributor
Posts: 10

Re: How to repeat a symput function to multiple observations

Posted in reply to novinosrin

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;

PROC Star
Posts: 1,571

Re: How to repeat a symput function to multiple observations

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 216 views
  • 8 likes
  • 3 in conversation