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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.