How to - Replace/Change Variables Values Dynamically

[Cynthia_sas]

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

[turcay]

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

[Cynthia_sas]

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

[turcay]

@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

[Ksharp]

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;