SAS - Reverse order of values of a Variable based

问题:

To summarize, I am working on parsing sentences of documents into single sentences. I need this for a bigger cause.

What I have is a table 'Position' which gives me the document id (ID) and the sentence number of a sentence in that document (NUM). I also have a column to display the sentence parsed (SENTENCE).

So an example table would be:

ID NUM SENTENCE
1   3    Hello
1   2    How are you
1   1    Fine
2   2    Thank you
2   1    You're Welcome
3   1    Nice Weather

What this means is that there were three sentences identified in document 1, two in document 2 and so on.

The problem is that (ID, NUM) = (1,3) or "Hello", refers to the third sentence in document 1, and (ID, NUM) = (1, 1) or "Fine", refers to the first sentence in document 1,and that's how the pattern continues for all other documents as well.

I'd like to reverse the order of NUM per ID, to give better clarity of the sentence order. This is also needed for feeding into other parts of my program.

Essentially, I'd like my output table to look like:

ID NUM SENTENCE
1   1    Hello
1   2    How are you
1   3    Fine
2   1    Thank you
2   2    You're Welcome
3   1    Nice Weather

I would greatly appreciate any assistance on how to perform this.

EDIT:

I would like to change the output to reflect the interchanging of sentence numbers in the output table. The order of the sentences would remain the same. This is so 'Hello' would be represented as sentence 1, as opposed to 3.

So hence, I would like only the 'NUM' variable interchanged within each 'ID' variable value in the output table.

回答1:

proc sort data=position;
  by ID NUM;
run;

EDIT: I think I misunderstood the question. What you really want is:

proc sort data=position;
  by ID descending NUM;
run;

data want (rename=(new_NUM=NUM));
  set position (drop=NUM);
  by ID;
  if first.ID then new_NUM = 1;
  output;
  new_NUM + 1;
run;

回答2:

A sequence number within a group can be easily computed using a SUM statement. The syntax is <variable> + <expression>;

Presume the sentence values within the group are in the desired order, but need a new associated within group sequence number starting from 1.

data want;
  set have (rename=(num=num_original));
  by ID;
  if first.ID then num = 1; else num + 1;
run;

回答3:

something like this should work in Proc SQL

proc sql;
create table want as 
select id, (max(num)+1)-num as num, sentence
from have
group by id;