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

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

deleted_user — Thu, 14 Jan 2010 10:35:14 GMT

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?

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

LinusH — Thu, 14 Jan 2010 11:10:38 GMT

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

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

DanielSantos — Thu, 14 Jan 2010 11:54:48 GMT

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

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

deleted_user — Thu, 14 Jan 2010 12:38:56 GMT

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.

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

LinusH — Thu, 14 Jan 2010 12:51:36 GMT

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

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

deleted_user — Thu, 14 Jan 2010 13:32:39 GMT

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.

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

DanielSantos — Thu, 14 Jan 2010 16:24:44 GMT

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.

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

DanielSantos — Thu, 14 Jan 2010 16:24:51 GMT

double post, please ignore.

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

data_null__ — Thu, 14 Jan 2010 16:54:23 GMT

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]