topic Re: How to - Replace/Change Variables Values Dynamically in SAS Programming

How to - Replace/Change Variables Values Dynamically

turcay — Sun, 24 Jul 2016 14:54:08 GMT

Hello everyone,

I want to replace/change variables values depending on their orders.

I have a sample code as below, I want to replace Order variable values with each other but depending on "DEV" and "REC" values.

If you can examine my followimg sample, you can understand what I try to do. I would also like to point out that "Have" data set can include more than for rows. So Order of "REC" value should replace with latest value of "Order"

Can somebody help me, please?

Data Have;
Length DataSet $ 32 Preffix $ 32 Order 8;
Infile Datalines Missover;
Input DataSet Preffix Order;
Datalines;
Have Mdl 1
Want Dev 2
Last Rec 3
Desired Val 4
;
Run;

Data Want;
Set Have;
If Upcase(Preffix)="DEV" Then Order=1;
If Upcase(Preffix)="REC" Then Order=4;
/*There can be more than four data set
so I need to assign this value to latest "Order" value*/
/*If Upcase(Preffix)="DEV" Then Do;*/
/*Order=TranWrd(Order,2,1);*/
/*Order=TranWrd(Order,1,2);*/
/*End;*/
/*If Upcase(Preffix)="REC" Then Do;*/
/*Order=TranWrd(Order,3,4);*/
/*Order=TranWrd(Order,4,3);*/
/*End;*/
Run;

Desired ->

What if "Have" data set will be as below, how can I replace order of "REC" with latest row?

Data Have;
Length DataSet $ 32 Preffix $ 32 Order 8;
Infile Datalines Missover;
Input DataSet Preffix Order;
Datalines;
Have Mdl 1
Want Dev 2
Last Rec 3
Want2 Val2 4
Desired Val 5
Have2 Mdl2 6
;
Run;

Thank you

Re: How to - Replace/Change Variables Values Dynamically

Cynthia_sas — Sun, 24 Jul 2016 15:28:13 GMT

Hi, I don't understand what you want. Are you saying that no matter what the value of the internal order variable is, if the value of Preffix is 'Dev' then the order changes to 1 and if the value is 'Rec', then that is always the Last order??? What about the ones between the first and last? If you make this modification to your posted code, creating a variable called origOrder, so we can see what the order value was originally, I do not understand the logic for the desired output your show in your screen shot...for example "Have" is originally order=1, what is the logic that makes it order=2 and "Desired" is originally order=4, what is the logic that makes it order=3. Noone can help you with the second question until you explain the logic of the what you first showed.

Here is the modified version of your program. Can you explain the logic behind how the other rows get renumbered???

cynthia

**the code;

Data Have;
Length DataSet $ 32 Preffix $ 32 origOrder 8;
Infile Datalines Missover;
Input DataSet Preffix origOrder;
Datalines;
Have Mdl 1
Want Dev 2
Last Rec 3
Desired Val 4
;
Run;

title;
Data Want;
Set Have;
If Upcase(Preffix)="DEV" Then Order=1;
else If Upcase(Preffix)="REC" Then Order=4;
else order=.;
Run;

proc print data=want;
run;
title;

Re: How to - Replace/Change Variables Values Dynamically

Astounding — Sun, 24 Jul 2016 15:55:35 GMT

It's a mistake to try to do this in one step. Just assign some extreme values:

data want;

set have;

if upcase(preffix)='DEV' then order=-5;

else if upcase(preffix)='REC' then order=99999;

run;

proc sort data=want;

by order;

run;

data want;

set want;

order = _n_;

run;

The data set is small, so take advantage of that. Use multiple easy programming steps.

Re: How to - Replace/Change Variables Values Dynamically

KachiM — Sun, 24 Jul 2016 17:49:14 GMT

Here is an Array way.

I use array to keep the Observation Number(Record ID). Then I exchange the positions of Record IDs depending on 'Dev' and 'Rec'.

But the data set is used 3 times which I think is a limitation unless proved otherwise.

data want;
   if _N_ = 1 then do;
      array k[7] _temporary_;
      do i = 1 by 1 until(eof);
         set have end = eof;
         k[Order] = i;
      end;
   end;
   do i = 1 by 1 until(last);
      set have end = last;
      if upcase(Preffix) = 'DEV' then do;
         k[dim(k)] = k[1];
         k[1] = i;
         k[i] = k[dim(k)];
      end;
      if upcase(Preffix) = 'REC' then do;
         k[dim(k)] = k[4];
         k[4] = i;
         k[i] = k[dim(k)];
      end;
   end;
   if eof;
      do i = 1 to dim(k) - 1;
         pt = k[i];
         set have point = pt;
         Order = i;
         output;
      end;
drop i;
run;

For 6 observations, the array is sized to 7. The 7th cell is used for exchanges. The first do-loop assigns the RID to the array cells. The second do-loop exchanges the RIDs. The third do-loop uses POINT= option to get the desired observation.

We can avoid the 3 passes over the data set by holding other two variables in two separate arrays and do the exchanges on them whenever there is exchange in ORDER.

Anyway this fun with Array.

Re: How to - Replace/Change Variables Values Dynamically

turcay — Sun, 24 Jul 2016 19:16:23 GMT

@Cynthia_sas,

Yes, this is what I want -> "Are you saying that no matter what the value of the internal order variable is, if the value of Preffix is 'Dev' then the order changes to 1 and if the value is 'Rec', then that is always the Last order???"

Actually, there is no logic, these values into the "Have" data set is an input data. Some person will fill the values then I will order depending on "DEV" and "REC". "DEV" should be first and "REC" should be last.

Re: How to - Replace/Change Variables Values Dynamically

turcay — Sun, 24 Jul 2016 19:30:21 GMT

@Astounding,

Your reply gives me what I want, Thank you.

To decrease data step count, if I write Proc Sql as below, Is it affect the performance positive or negative?

Also is it possible for me to add your Order=_N_ step into the PROC SQL step?

Proc Sql;
Create Table Want As
Select Dataset
,Preffix
,Case
When Upcase(Preffix)="DEV" Then -5
WHEN Upcase(Preffix)="REC" Then 99999
Else Order
End AS Order
From Have
Order By Order;
Quit;

 
Data Want2;
Set Want;
Order=_N_;
Run;

Thank you

Re: How to - Replace/Change Variables Values Dynamically

turcay — Sun, 24 Jul 2016 19:38:32 GMT

@KachiM,

Thank you very much for your detailed information. But I did not understand what should I do, if there are six observation?

In your method, I need to know count of observations number, is it possible to perform your method without knowing the observation count. Because the observation count can change.

Thank you

Re: How to - Replace/Change Variables Values Dynamically

KachiM — Sun, 24 Jul 2016 19:58:48 GMT

A small program get it.

data _null_;
   if 0 then set have nobs = n;
   call symputx('nplusone', n + 1);
stop;
run;
%put &nplusone;

Place &nplusone wherever 7 appears.

Re: How to - Replace/Change Variables Values Dynamically

turcay — Sun, 24 Jul 2016 21:16:10 GMT

Thank you very much,

If I change data set as below and replace 7 with &nplusone. the result doesn't become what I want.

Data Have;
Length DataSet $ 32 Preffix $ 32 Order 8;
Infile Datalines Missover;
Input DataSet Preffix Order;
Datalines;
Have Mdl 1
Want Dev 2
Last Rec 3
Want2 Val2 4
Desired Val 5
Have2 Mdl2 6
;
Run;

data _null_;
   if 0 then set have nobs = n;
   call symputx('nplusone', n + 1);
stop;
run;
%put &nplusone;

data want;
   if _N_ = 1 then do;
      array k[&nplusone.] _temporary_;
      do i = 1 by 1 until(eof);
         set have end = eof;
         k[Order] = i;
      end;
   end;
   do i = 1 by 1 until(last);
      set have end = last;
      if upcase(Preffix) = 'DEV' then do;
         k[dim(k)] = k[1];
         k[1] = i;
         k[i] = k[dim(k)];
      end;
      if upcase(Preffix) = 'REC' then do;
         k[dim(k)] = k[4];
         k[4] = i;
         k[i] = k[dim(k)];
      end;
   end;
   if eof;
      do i = 1 to dim(k) - 1;
         pt = k[i];
         set have point = pt;
         Order = i;
         output;
      end;
drop i;
run;

Thank you

Re: How to - Replace/Change Variables Values Dynamically

KachiM — Sun, 24 Jul 2016 22:57:18 GMT

This is the output I am getting. Is this OK or not?

 Obs    DataSet    Preffix    Order

  1     Want        Dev         1
  2     Have        Mdl         2
  3     Want2       Val2        3
  4     Last        Rec         4
  5     Desired     Val         5
  6     Have2       Mdl2        6

Re: How to - Replace/Change Variables Values Dynamically

turcay — Sun, 24 Jul 2016 23:19:11 GMT

Actually, it should be the following table;

Re: How to - Replace/Change Variables Values Dynamically

turcay — Sun, 24 Jul 2016 23:22:50 GMT

@Astounding,

According to your response, I would like to ask one more question to you. What if I don't have Order variable in the data set, can I still order the table the way I want?

Thank you

What if have following table;

Data Have;
Length DataSet $ 32 Preffix $ 32;
Infile Datalines Missover;
Input DataSet Preffix ;
Datalines;
Have Mdl
Want Dev
Last Rec
Want2 Val
Desired Val
Have2 Mdl2
;
Run;

Re: How to - Replace/Change Variables Values Dynamically

KachiM — Mon, 25 Jul 2016 00:11:23 GMT

Yes.

Replace Order by _N_ in the first do-loop whic is i here.

Edited.....

      do i = 1 by 1 until(eof);
         set have end = eof;
         k[i] = i;
      end;

Re: How to - Replace/Change Variables Values Dynamically

Cynthia_sas — Mon, 25 Jul 2016 00:08:26 GMT

Hi:
Pardon my confusion.

Why did you post data with an ORDER and go on and on about needing a new ORDER variable if your data does not have an ORDER variable in the first place?

Of course, when you read the data, you could make the original order variable by assigning _N_ to the ORDER variable and then go through the rest of the steps as previously shown by other participants.

cynthia

Re: How to - Replace/Change Variables Values Dynamically

Astounding — Mon, 25 Jul 2016 00:43:23 GMT

Yes, assuming your SQL code is working you can cut out a step that way. But these data sets are small. The most efficient method doesn't depend on the number of steps. Use whatever you would find easy to understand when you are reviewing the program a year from now.

Re: How to - Replace/Change Variables Values Dynamically

Ksharp — Mon, 25 Jul 2016 02:29:55 GMT



Data Have;
Length DataSet $ 32 Preffix $ 32 Order 8;
Infile Datalines Missover;
Input DataSet Preffix Order;
Datalines;
Have Mdl 1
Want Dev 2
Last Rec 3
Want2 Val2 4
Desired Val 5
Have2 Mdl2 6
;
Run;

data want; 
 if _n_=1 then do;
  if 0 then set have(rename=(Order=_Order Preffix=_Preffix));
  declare hash h(dataset:'have(rename=(Order=_Order Preffix=_Preffix) where=(_Preffix in ("Dev" "Rec")))');
  h.definekey('_Preffix');
  h.definedata('_Order');
  h.definedone();
 end;
set have nobs=nobs end=last;
if _n_=1 then do;
 _Preffix='Dev'; h.find(); Order=_Order;
end;
if Preffix='Dev' then Order=1;
if Preffix='Rec' then Order=nobs;
if last then do;
 _Preffix='Rec'; h.find(); Order=_Order;
end;
drop _:;
run;

Re: How to - Replace/Change Variables Values Dynamically

KachiM — Mon, 25 Jul 2016 13:21:27 GMT

In previous post input data set had 3 passes. In the following program, it needs one pass to store the Record IDs in an array(K) and another pass to directly to go to the required record for output. As records are processed the Preffix variable is checked for 'DEV' and 'REC' and if they found, their RID is saved in another array(P). Once the RIDs are in the array(K), comparing with (P), exchanges are made. Finally the array(K) has the required order of RIDs. The final do-loop uses cell values of K to directly hit the record for the output.

A small modification to the data set is made. No need for the ORDER variable in the input data set. Here is everything:

Data have;
Length DataSet $ 32 Preffix $ 32;
Infile Datalines Missover;
Input DataSet Preffix;
datalines;
Have Mdl
Want2 Val2
Desired Val
Have2 Mdl2
have3 Mdl3
want2 Val3
have4 Mdl4
have5 Mdl5
Last Rec
Want Dev
;
run;

data _null_;
   if 0 then set have nobs = n;
   call symputx('nplusone', n + 1);
stop;
run;
%put &nplusone;

data want;
   if _N_ = 1 then do;
      array k[&nplusone] _temporary_;
      array p[&nplusone] _temporary_;
      do i = 1 by 1 until(eof);
         set have end = eof;
         k[i] = i;
         if upcase(Preffix) = 'DEV' then p[i] = 1;
         else if upcase(Preffix) = 'REC' then p[i] = 4;
      end;
      do i = 1 to dim(k) - 1;
         if not missing(p[i]) then do;
            k[dim(k)] = p[i];
            k[p[i]] = i;
            k[i] = k[dim(k)];
         end;
      end;
   end;
   if eof;
      do i = 1 to dim(k) - 1;
         pt = k[i];
         set have point = pt;
         Order = i;
         output;
      end;
      stop;
drop i;
run;

proc print data = want;
run;


  DataSet    Preffix    Order

  Want        Dev          1
  Want2       Val2         2
  Desired     Val          3
  Last        Rec          4
  have3       Mdl3         5
  want2       Val3         6
  have4       Mdl4         7
  have5       Mdl5         8
  Have2       Mdl2         9
  Have        Mdl         10

Re: How to - Replace/Change Variables Values Dynamically

turcay — Mon, 25 Jul 2016 16:55:31 GMT

Hello @Cynthia_sas,

The data which I wrote, is an input table so it can change not because of me. That is the reason why also I want to ask what if Order variable is not contained, question.

The method which I did as below. I think you also suggested me the sample method. If I understood incorrect, I'm sorry 😞

Data Want;
Set Pre_Want;
Order=_N_;
Run;

Proc Sql;
Create Table Want As
Select Dataset
,Preffix
,Case
When Upcase(Preffix)="DEV" Then -5
WHEN Upcase(Preffix)="REC" Then 99999
Else Order
End AS Order
From Have
Order By Order;
Quit;

Data Want2;
Set Want;
Order=_N_;
Run;

Thank you

Re: How to - Replace/Change Variables Values Dynamically

Cynthia_sas — Mon, 25 Jul 2016 17:31:58 GMT

Hi:
Yes, I think you're correct. Ksharp has also provided a Hash Table approach. As a check on the level of difficulty, we teach Hash Tables in Programming 3, so if you have a simple method that works for you, especially since you have so few rows, that is probably the better approach. Especially if you need to turn this code over to folks who are less experienced at using SAS than you are. Although HASH tables are very powerful, I think they may be too much for what your aim is.

BTW, when you are getting data from someone, you say that "The data ... is an input table so it can change not because of me" -- if you are allowing other folks to change the table they give you, that generally is a sign that the requirements are still in flux and that you are going to be constantly fiddling with/modifying your program to suit the data they send you. If the users (I assume it is users who are sending you the possibly changeable input table) cannot agree on a format for this table you need, then I would recommend getting them to agree on a standard way to send you the data. Otherwise, you will not ever be able to turn anything over for production.

cynthia

Re: How to - Replace/Change Variables Values Dynamically

turcay — Mon, 25 Jul 2016 21:47:55 GMT

@Cynthia_sas,

Thank you so much for your warm approach .Your approach and your reccommendation are much appreciated.

At this moment, learning Hash is also far from me, it seems difficult but I can guess how powerful Hash code is . I need a time to converge it.

About your second reccomandation you are totally right. This statement is what I'm facing nowadays -> that generally is a sign that the requirements are still in flux and that you are going to be constantly fiddling with/modifying your program to suit the data they send you.

Thank you