topic how to skip a variable based on the value and go to the next variable. in SAS Programming

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

bibbnd — Sat, 24 Jul 2021 00:51:52 GMT

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;

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

jimbarbour — Sat, 24 Jul 2021 01:11:01 GMT

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

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

jimbarbour — Sat, 24 Jul 2021 01:36:43 GMT

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;

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

Patrick — Sat, 24 Jul 2021 02:37:23 GMT

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

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

bibbnd — Sun, 25 Jul 2021 21:52:35 GMT

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

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

bibbnd — Sun, 25 Jul 2021 22:01:55 GMT

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.

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

BeatriceWang — Mon, 26 Jul 2021 18:25:33 GMT

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.

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

jimbarbour — Mon, 26 Jul 2021 19:25:19 GMT

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-the-next/m-p/756345#M238754

Jim

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

BeatriceWang — Mon, 26 Jul 2021 19:54:53 GMT

Thank you.
will do.