Hi,
I'd like to search a column in a dataset based on some conditions. Then replace them with a sequence with range [0,1] and 1/n as increment (n is the number of data found based on the condition). For example, search odd numbers in the column j in the Test dataset below. Then replace '3, 5, 7, 9, 11' with '0.2, 0.4, 0.6, 0.8, 0.1'.
data test; do i=1 to 10 by 1; j=i+1; output; end; run;
Many thanks in advance
data WANT;
do until(LASTOBS1);
set HAVE(where=(mod(J,2)=1)) end=LASTOBS1;
DENOM+1;
end;
N=START_RANGE;
do until(LASTOBS2);
set HAVE end=LASTOBS2;
if mod(J,2)=1 then do;
J=N;
N+(END_RANGE-START_RANGE)/DENOM;
end;
output;
end;
run;
Like this?
data WANT;
set HAVE;
if mod(J,2)=1 then do;
N+0.2;
J=N;
end;
run;
Do you want 2 steps? 1) Count the number of odd number 2) Apply the derived ratio?
Yep, 2 steps. Also is it possible we can do it with the dynamic range defined in the columns like column start_range and end_range as below?
data test; do i=1 to 10 by 1; j=i+1; start_range=0.5; end_range=1; output; end; run;
> Also is it possible we can do it with the dynamic range
Just change the line
N+1/DENOM;
data WANT;
do until(LASTOBS1);
set HAVE(where=(mod(J,2)=1)) end=LASTOBS1;
DENOM+1;
end;
N=START_RANGE;
do until(LASTOBS2);
set HAVE end=LASTOBS2;
if mod(J,2)=1 then do;
J=N;
N+(END_RANGE-START_RANGE)/DENOM;
end;
output;
end;
run;
Many thanks. It is exactly what I wanted. Can I have the last request? How can I calculate N by group? For example, the test dataset has a group column. I want to count the number of the odd numbers by the group. Then set the two subgroups with the different increments. Appreciate your help.
data test;
do i=1 to 10 by 1;
j=i+1;
if j<=5 then
group = 0;
else
group = 1;
start_range=0.5;
end_range=1;
output;
end;
run;
> How can I calculate N by group?
Like this ?
data WANT;
do until(LASTOBS1);
set HAVE(where=(mod(J,2)=1)) end=LASTOBS1;
DENOM0+(GROUP=0);
DENOM1+(GROUP=1);
end;
do until(LASTOBS2);
set HAVE end=LASTOBS2;
if mod(J,2)=1 then do;
if GROUP=0 then do;
N0+1/DENOM0;
J=N0;
end;
if GROUP=1 then do;
N1+1/DENOM1;
J=N1;
end;
end;
output;
end;
run;
Assuming the data is not sorted by GROUP.
Like this?
data WANT;
call missing(DENOM,N);
do until(last.DATE);
set HAVE(where=(mod(J,2)=1));
by DATE;
DENOM+1;
end;
do until(last.DATE);
set HAVE ;
by DATE;
if mod(J,2)=1 then do;
N+1/DENOM;
J=N;
end;
output;
end;
run;
Untested as I don't have access to SAS atm.
This won't work if some groups contain no odd numbers.
Like this?
data WANT;
do until(LASTOBS1);
set HAVE(where=(mod(J,2)=1)) end=LASTOBS1;
DENOM+1;
end;
do until(LASTOBS2);
set HAVE end=LASTOBS2;
if mod(J,2)=1 then do;
N+1/DENOM;
J=N;
end;
output;
end;
run;
As you have seen, you need to pass through the data twice: first time to establish a denominator, and the second time to increment the cumulative fraction and output. You can do this with a pair of loops each with a SET statement, or a single SET with "in=" parameters, and a couple of "summing statements":
data want (drop=_:);
set have (in=firstpass)
have (in=secondpass) ;
where mod(j,2)=1;
if firstpass then _denom+1; /*summing statement for counting*/
if secondpass ; /*"subsetting if" */
n+1/_denom ; /* summing statement for incrementing */
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.