DATA Step, Macro, Functions and more

How to move a record to a specific place in a data set.

Reply
N/A
Posts: 0

How to move a record to a specific place in a data set.

In my data set there are 2 variables VAR and FREQUENCY.

The number of records are 10 and the 10 values of VAR are unique.

If VAR="X" is placed among observations number 1 - 5 I want to get it as observation number 6. The relative order of the 9 other observations should be kept.

How can that be done?
Super User
Posts: 5,260

Re: How to move a record to a specific place in a data set.

Is this a real life problem, or just a narrowed down example?
How does X relates to the other values of VAR? And how was the original sort order of data created?
Without knowing the real purpose, I would suggest that you create a look-up table, which specifies the sort order according to VAR, join and then sort.

/Linus
Data never sleeps
Super Contributor
Posts: 474

Re: How to move a record to a specific place in a data set.

Yes... Quite an intriguing question.

Knowing the purpose would help, perhaps to point out another alternative.

Anyway, if I got this right, you want to reposition some observation within the dataset.

Being X=3 for example, you could try this:
[pre]
data OUTDATA;
set INDATA (where=(VAR ne 3)); * exclude the desired obs;
output;
if _N_ eq 6-1 then do;
set INDATA (where=(VAR eq 3)); * include the desired obs
output;
end;
run;
[/pre]
Cheers from Portugal.

Daniel Santos @ www.cgd.pt
N/A
Posts: 0

Re: How to move a record to a specific place in a data set.

VAR is character variable, so "X" is an allowed value. VAR can be any capital letter. The records are sorted in descending order by the variable FREQUENCY.

If the observation VAR="X" are among observations number 1 - 5, I want to have it has observation number 6.

The example is narrowed down.
Super User
Posts: 5,260

Re: How to move a record to a specific place in a data set.

Now we understand the sort order.
But not why "X" should be in obs 5. Should it always be in position 5, or depending on the situation?
You could just rank your observations, and for VAR="X" re-rank it to 5 (and potentially change the existing no 5), and then sort the data.
/Linus
Data never sleeps
N/A
Posts: 0

Re: How to move a record to a specific place in a data set.

Don't people on this forum simplify their problems?

I simplify because I don't want to reveal confidental data, and I simplify because it should be easier to answer the question if the example is narrowed down.

In my data set is a frequency table. If the frequency for the capital letter "X" is among the 5 (FIVE) biggest I want to have it as observation number 6 (SIX). The other 9 records should keep their relative order.

Please, don't ask me why I want to have the record with "X" as observation number 6, and only when it first was among records number 1 - 5. If the record with VAR="X" was placed among observations number 6 - 10 when I got the data set, nothing should be done.
Super Contributor
Posts: 474

Re: How to move a record to a specific place in a data set.

ErnestoC, I believe no one here is interested about you're confidential data whatsoever.

Sometimes questions here are dropped with so little information that the effort to give a proper answer is doubled (one to understand the question/scenario and the other to ACTUALLY give a proper solution).

That said, I believe the suggestion (just swap the numeric VAR to a char one) I have posted before will solve you're trouble.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.
Super Contributor
Posts: 474

Re: How to move a record to a specific place in a data set.

double post, please ignore.
Respected Advisor
Posts: 3,777

Re: How to move a record to a specific place in a data set.

This seems a bit complicated but I believe it works properly when the target(t) is not in the first 5 obs.

[pre]
proc plan seed=412257359;
factors x=10 f=1 of 70;
output out=test;
run;
quit;

proc freq order=freq;
tables x / out=f;
weight f;
run;
proc print;
run;
%let t = 3;
data test;
do g = 1 to 5;
set f(obs=5);
if x = &t then leave;
end;
if 1 le g le 5 then do;
do point=1 to g-1, g+1 to 6, g, 7 to nobs;
set f point=point nobs=nobs;
output;
end;
end;
else do until(eof);
set f end=eof;
output;
end;
stop;
run;
proc print;
run;
[/pre]
Ask a Question
Discussion stats
  • 8 replies
  • 219 views
  • 0 likes
  • 4 in conversation