07-10-2013 01:39 AM
I have a dataset with variable id and time among many. I created a variable count to count the number of observations for each id in each time. For example, for id 1 and time 1, there are totally 30 observations, the count variable will show 1, 2, until 30, for id 1 time 2, there are totally 36 observations, count will be 1 to 36. Now I want to drop any id that has less than 36 observations. What code should I use? I tried to use where count=36, but apparently that does not work as it will only keep observations with count variable = 36. Thanks.
07-10-2013 02:38 AM
This cannot work b/c I want only ids that have full 36 observations. Please see example below.
id time count
1 1 1
1 1 2
1 1 30
1 2 1
1 2 2
1 2 36
I want to delete all obs with id=1 and time=1 since only 30 observations for id=1 and time=1. I want to keep all obs with id=1 and time=2.
07-10-2013 03:04 AM
rewriting my understanding..
you are having a data, It has 30 observation with ID=1 and time =1 and it also has 36 observation with ID=1 and time =2.
You just want to keep only those observation which have ID=1 and Time =2.
i am just writing only one if statement for this...
If Id=1 and time =2 then output datasetname; * it would create a new data set *
If id=1 and time=1 then delete;
else output DSN;
if id=1 and time=2;
Kindly correctly me, if i am wrong..
07-10-2013 05:45 PM
HI, you are right, but I have a huge dataset with different id and time. It's impossible for me to write the way you showed. I cannot predict which id and time combination meets requirement.
07-10-2013 07:26 AM
Is this what you are trying to achieve?
CREATE TABLE WANT AS
) A /*Creates an Alias for use in the where clause*/,
MAX(COUNT) AS MAX_COUNT
GROUP BY ID, TIME1
HAVING CALCULATED MAX_COUNT >= 36
) B /*Creates an Alias for use in the where clause*/
WHERE A.ID = B.ID
AND A.TIME1 = B.TIME1
ORDER BY ID, TIME1, COUNT
If you aren't familiar with SQL you could also use a SAS merge as follows, which is more efficient.
MERGE HAVE (IN=A WHERE=(COUNT >= 36))
BY ID TIME1;
IF A AND B;
I hope that this is what you were after.
07-10-2013 05:48 PM
Hi Scott. You are right in this way, but a bit long in coding compared to the one solution above. Thanks anyway.
07-10-2013 09:06 AM
If i understand correctly here is the solution.
with the bellow code you will get observations that ID have full 4 observations.others can be deleted.
input ID time;
if First.time then count=0;
Select Max(Time) into :Values separated by ' ' from aa
Group by Time
having max(Count) lt 4;
if time in (&values) then delete;
Message was edited by: sajeev kuridi
Need further help from the community? Please ask a new question.