DATA Step, Macro, Functions and more

Two types of records; two selection rules.

Reply
N/A
Posts: 0

Two types of records; two selection rules.

ID_ X_Var1_Var2 ...
1 _0
1_1
2_0
2_0
2_0
.
.
For each ID, one and only one record should be kept. When there is at least one record with X=1 one selection rule applies. When X=0 in every record another selection rule applies. (The different selection rules use the other variables; Var1, Var2, ..., in different ways.)

There could be situations when the data set contains only one type of records, so I can't split the the data into two sets and use the different rules for each set, and then join them.

The program should be general.

Any idea on how to solve this, would be much appreciated.
Super Contributor
Super Contributor
Posts: 3,174

Re: Two types of records; two selection rules.

Posted in reply to deleted_user
Using PROC MEANS/SUMMARY, build a file with MAX(X) BY ID, then MERGE that file back onto the (sorted) original file so you can detect the "at least one" X=1 condition, then use IF / THEN logic to perform the conditional logic. As suggested before, break down your overall "task" into individual requirements, program for each, then combine into a single SAS program.

Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: Two types of records; two selection rules.

When there was an ID with at least one record with X=1 then a sorting could be done BY ID VAR1 VAR2 and VAR3, and I could use the condition

IF LAST.ID;

to select the right record.

When all records have X=0, the sorting should be done BY ID VAR4 and VAR5, and the condition would again be

IF LAST.ID;

I don't understand your suggestion.
PROC Star
Posts: 7,489

Re: Two types of records; two selection rules.

Posted in reply to deleted_user
Ernesto,

If the values of X are only 1 and 0, then why not approach it differently. If you sort you data by id and descending X, then you could use a simple data step to separate the records into two groups. e.g.:

data atleastone1 (drop=group) allzeros (drop=group);
set have;
by id;
retain group;
if first.id then do;
if x eq 1 then group=1;
else group=0;
end;
if group eq 1 then output atleastone1;
else output allzeros;
run;

Then you will have two different datasets that you can sort and analyze as you describe.

Art
N/A
Posts: 0

Re: Two types of records; two selection rules.

That's what I have done.

Anyone(but not both) of the data sets could then be empty after splitting the original data set.

The program was supposed to be used by other persons with new data periodically and the program should be general.

I didn't think it was possble to join a non-empty set with an empty set. That was the problem for me. But I have now found out that code like this works:

data joined_data;
set non_emptyset emptyset;
run;

I should have checked before I asked the forum.

Thank's.
Ask a Question
Discussion stats
  • 4 replies
  • 186 views
  • 0 likes
  • 3 in conversation