I need to reorder the variables based on removing a value that i do not want. If the grid_rate =D than i dont want that value and go on the the next grid rate. I WANT TO CREATE A NEW RATE WITH THE NEW ORDER FOR EACH CONTRACT ID.
my code is not picking up the D in the records .to create the correct values in the new_rate variables.
here is the data
contract_id rate1 rate2 rate3 rate4 new_rate1 new_rate2 new_rate3 new_rate4 ;
XEA123 D PRIME TD49 TD58 PRIME TD49 TD58
DEW343 TD49 D TD58 PRIME TD49 TD58 PRIME
JRQ912 PRIME TD49 TD58 PRIME TD49 TD58
DATA NEM2;
SET NEM;
array rate[*] $7 GRID_rate1 GRID_Rate2 GRID_rate3 GRID_RATE4;
array x{4} $7 _temporary_;
array k[4] $7 new_rate1 new_Rate2 new_rate3 new_RATE4;
*initializing new variables;
n=0;
do i=1 to dim(rate);
if RATE{i} ne 'D' then do;
n+1;
x{n}=RATE{i};
end;
else k{n}=' ';
end;
do i=1 to dim(rate);
k{i}=x{i};
end;
drop i n;
@bibbnd Something like below should do.
data have;
infile datalines truncover dsd;
input (contract_id rate1 rate2 rate3 rate4 want_rate1 want_rate2 want_rate3 want_rate4) ($);
datalines;
XEA123,D,PRIME,TD49,TD58,PRIME,TD49,TD58
DEW343,TD49,D,TD58,PRIME,TD49,TD58,PRIME
JRQ912,PRIME,TD49,TD58,,PRIME,TD49,TD58
;
data want(drop=_:);
set have;
array rates {*} rate1 - rate4;
array new_rates {*} $8 new_rate1 - new_rate4;
do _i=1 to dim(rates);
if rates[_i] ne 'D' then
do;
_ind=sum(_ind,1);
new_rates[_ind]=rates[_i];
end;
end;
run;
Or if you want to change the rate variables "in place":
data want(drop=_:);
set have;
array rates {*} rate1 - rate4;
do _i=1 to dim(rates);
if rates[_i] ne 'D' then
do;
_ind=sum(_ind,1);
rates[_ind]=rates[_i];
end;
end;
_ind=sum(_ind,1);
do _i=_ind to dim(rates);
call missing(rates[_i]);
end;
run;
If it's not picking up the 'D', the first thing I would try is the STRIP() function. Like this:
if STRIP(RATE{i}) ne 'D' then do;
Jim
OK, no, STRIP() doesn't really fix things, but I see the problem. You're initializing N to 0. If on the first time in the DO loop the value is 'D', then, when it tries to execute
k{n} = ' ';
it's going to have a 0 as the subscript which is not valid. Instead, initialize to 1 and and move the
n + 1;
to after
x{n} = RATE{i};
See far below for full example. I lopped off the "new" rates from the Nem dataset since they'll be generated anyway. See immediately below for the results. By the way, that data looks suspiciously like the data I used to work with when I was a contractor for So Cal Edison. 🙂
Jim
DATA Nem;
INFILE DATALINES MISSOVER DSD DLM='09'X;
INPUT contract_id $
Grid_rate1 $
Grid_rate2 $
Grid_rate3 $
Grid_rate4 $
new_rate1 $
new_rate2 $
new_rate3 $
new_rate4 $
;
DATALINES;
XEA123 D PRIME TD49 TD58
DEW343 TD49 D TD58 PRIME
JRQ912 PRIME TD49 TD58
;
RUN;
DATA NEM2;
SET NEM;
array rate[*] $7 GRID_rate1 GRID_Rate2 GRID_rate3 GRID_RATE4;
array x{4} $7 _temporary_;
array k[4] $7 new_rate1 new_Rate2 new_rate3 new_RATE4;
*initializing new variables;
n=1;
do i=1 to dim(rate);
PUTLOG "NOTE: " i=;
if STRIP(RATE{i}) ne 'D' then
do;
x{n} = RATE{i};
n + 1;
end;
else
DO;
k{n} = ' ';
END;
end;
do i=1 to dim(rate);
k{i}=x{i};
end;
drop i n;
RUN;
@bibbnd Something like below should do.
data have;
infile datalines truncover dsd;
input (contract_id rate1 rate2 rate3 rate4 want_rate1 want_rate2 want_rate3 want_rate4) ($);
datalines;
XEA123,D,PRIME,TD49,TD58,PRIME,TD49,TD58
DEW343,TD49,D,TD58,PRIME,TD49,TD58,PRIME
JRQ912,PRIME,TD49,TD58,,PRIME,TD49,TD58
;
data want(drop=_:);
set have;
array rates {*} rate1 - rate4;
array new_rates {*} $8 new_rate1 - new_rate4;
do _i=1 to dim(rates);
if rates[_i] ne 'D' then
do;
_ind=sum(_ind,1);
new_rates[_ind]=rates[_i];
end;
end;
run;
Or if you want to change the rate variables "in place":
data want(drop=_:);
set have;
array rates {*} rate1 - rate4;
do _i=1 to dim(rates);
if rates[_i] ne 'D' then
do;
_ind=sum(_ind,1);
rates[_ind]=rates[_i];
end;
end;
_ind=sum(_ind,1);
do _i=_ind to dim(rates);
call missing(rates[_i]);
end;
run;
Thank you everybody for trying to provide the solution.
I got a similar situation.
I tried the below solution.
It seems working for me by performing some of the eye ball checking.
I need to conduct a data quality check.
Can somebody who has been responding in this thread suggest an efficient approach (SAS code) to conduct a data quality check to ensure it has done the job appropriately?
Thank you very much in advance.
Hi, @BeatriceWang,
Please post a new question. Once a problem is solved, a lot of people won't ready any further comments. You can reference this thread as follows: https://communities.sas.com/t5/SAS-Programming/how-to-skip-a-variable-based-on-the-value-and-go-to-t...
Jim
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.