How to group this kind of data

[zana]

So thanks, Arth.

Unfortunately this code had tow errors, too:

[ERROR: Undeclared data symbol Previous"Current for hash object at line 1585 column 3.

ERROR: DATA STEP Component Object failure.  Aborted during the EXECUTION phase.]

I can not found any information for group column.

                                                    want

zana

[Haikuo]

What version of SAS you are using? I believe most of the Hash solutions in this thread are for 9.2 or later.

Haikuo

[zana]

Thanks Hai I use SAS 9.1 & 9.2. With run your code (with both version) some error appear. e.g., NOTE: There were 11 observations read from the data set WORK.HAVE. ERROR: Invalid data set name at line 5316 column 13. ERROR: Hash data set load failed at line 5316 column 13. ERROR: DATA STEP Component Object failure.  Aborted during the EXECUTION phase. NOTE: The SAS System stopped processing this step because of errors. zana

[TomKari]

Hi, Zana

If I'm reading your post correctly, the only difference is that in my output the Group code is the latest number, and in yours (and in the OP's),

the desired Group code is the earliest number.

The reason for this is that the links are in "previous-current" order, which means that for some current observations, the previous is missing, which causes PROC BOM to cough a hairball.

As an experiment, if we recast our data into "current-next" order, and run the following code, it is successful as PROC BOM doesn't care if some next

values are missing (I'm only using the ID 1 data, to simplify by skipping the combination step).

data have;
infile cards dlm='09'x;
input current next;
cards;
6 7
7 8
8 .
21 25
25 .
30 26
35 30
33 35
26 .
run;

proc bom data=work.have out=work.want;
structure / part=current component=next;
run;

However, without changing the input I believe that the two PROC SQL statements that I've added will i) create a lookup table of what the Group should

be for a given incorrect group, and ii) apply it to the data.

proc sql;
create table _have as
  select ID, Previous, Current,
   ((ID * 1000) + Previous) as _Previous,
   ((ID * 1000) + Current) as _Current
  from work.have;
quit;

proc bom data=work._have out=work._want;
structure / parent=_Current component=_Previous ID=(ID Current Previous);
run;

proc sql;
create table _GroupIDs as select _PROD_, Current from work._want where Previous is null;
create table want as select w.ID, w.Previous, w.Current, g.Current as Group
from work._want w inner join work._GroupIDs g on(w._PROD_ = g._PROD_);
quit;

Using this, my results come out the same as yours.

  Tom

[zana]

Tom Unfortunately i can not run your code. Due to:

Do you think that this problem related with version of mine?

zana

[art297]

Zana,

You say that you have a dataset that contains id, current and previous, and then say that a program failed because your dataset didn't have two of those variables.

Please explain!

[TomKari]

Hi, Zana

My code is based on the presupposition that you have a SAS dataset named "work.have" that contains variables "Current", "Previous", and "ID", as discussed in the original post.

If your situation is different, let us know what you have, and we'll probably be able to work you around to a solution.

Tom

[zana]

Thank you for taking the time to help out on this. My situation was incorrect. Sincere apologies for this carelessness.

By run your complete code, i saw relatively unexpected output. Results of the first section is desired output (with a few extra columns). Why did you use extra code?

*First section of code;

proc bom data=work.have out=work.want;

structure / part=current component=next;

run;

Once again your help and timely response is much appreciated!

zana

[TomKari]

If your data is in "current-next" form, the PROC BOM will be all you need. The next bit of code is to swap the Group ID for the OP, which was in "previous-current" form.

The detailed explanation is a little too complicated for this forum, but I'm delighted you got the results you want!

Best,

  Tom

[zana]

So thanks for all your time your sincerely zana

[zana]

Dear Tom How can i do this format of data by complete code (when there has any dot symbol)?

data have;

infile cards;

input current next id;

cards;

7 6 1

8 7 1

25 21 1

26 30 1

30 35 1

35 33 1

run;

When i run it, i have any Group output.

My output is:

zana

[TomKari]

I suspect the reason is that we started out with "previous-current", and you then moved it to "current-next", so we have some Frankenstein code that's neither fish nor fowl.

However, since you gave us some excellent sample data, we can clean everything up.

The first step is to run the same SQL query, to combine the ID with the current and next values:

proc sql;
create table _have as
  select ID, Current, Next,
   ((ID * 1000) + Current) as _Current,
   ((ID * 1000) + Next) as _Next
  from work.have;
quit;

Exactly the same number of records will come out of this, just with two additional columns.

Because your data is in "current-next", the PROC BOM step becomes very simple:

proc bom data=work._have out=work._want;
structure / parent=_Current component=_Next ID=(ID Current Next);
run;

I didn't remove any variables from the output, so that you can see what the procedure produces. This is where your confusion is coming in, as BOM will add records for "current" values that don't appear as a "next". But except for these added records, I think that the output is exactly what you're looking for. Becuase you're using "current-next", the Group value (_Prod_ in the BOM output) is correct, i.e. the first value in the chain instead of the last, so there's not need to do any transformation of that.

The last step just removes the additional records that BOM added, transforms the combined value back into the needed value for Group, and removes the extraneous columns.

proc sql;
create table want as
  select ID, Current, Next,
   _Prod_ - (int(_Prod_ / 1000)*1000) as Group
  from work._want
  where Current is not null;
quit;

See if this is closer to what you're after.

Tom

[zana]

you are a magician Tom. Thanks a lot. zana