how to skip a variable based on the value and go to the next variable.

[bibbnd]

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;

[jimbarbour]

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

[jimbarbour]

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]

This did not work but just copied the values over to the new variables.

[bibbnd]

So it something about the D in my variable that is not being recognized. That is why said it didn't work. However, I did an include and it worked fine.

[Patrick · Accepted solution]

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

[BeatriceWang]

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.

[jimbarbour]

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

[BeatriceWang]

Thank you.
will do.