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.
1 | 2 | c | c | 1 |
2 | 1 | c | n | . |
3 | 1 | n | n | . |
4 | 2 | c | n | . |
@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;
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
@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;
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".
@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;
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
Do you mean both month1 and month2 will have to be C or any one of the two can be C?
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,
@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 -
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;
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
@TLSAS Always welcome and feel free. Take care
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;
@TLSAS Aah ok, ofcourse won;t work for the reason length(vname(t(_n_)) finds the last one.. Brb soon
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.