topic Re: delete rows in SAS Programming

delete rows

Amalik — Fri, 13 Apr 2018 01:56:17 GMT

Hi,

How do I delete rows with less than 60 observations with the following type of data set.

Fundid	200801	200802	200803	200804	200805	200806	200807	200808
1
2
3
4
5

Re: delete rows

Reeza — Fri, 13 Apr 2018 02:11:18 GMT

Observation is typically a row, so the way you're using that term doesn't align.

How are you defining an observation? How would you calculate how many obs in a data set to implement the rule?

Can you show an expanded sample data set and an illustration of what you would expect as output?

Re: delete rows

Amalik — Fri, 13 Apr 2018 02:17:04 GMT

For example, I have the following dataset,

Fundid	200801	200802	200803	200804	200805	200806	200807	200808
1	3	4 5	6 7	8	8
2	56	23	6	3	4	56
3	65	87	3	6	8	23
4
5

I would like to keep those fundid's with atleast 6 observations and delete those with less than 6 observations. So I would want the output to look like this,

Fundid	200801	200802	200803	200804	200805	200806	200807	200808
2	56	23	6	3	4	56
3	65	87	3	6	8	23

Re: delete rows

Reeza — Fri, 13 Apr 2018 02:22:48 GMT

Ok, my translation would be to : You want observations (rows) with 6 non-missing values in the date variables (200801-200808).

1. Create an array of the date variables

2. Use N() function to determine the number of non-missing values

3. If results #2 < 6 then delete.

array dates(*) Month200802 - Month201202; /*your column names are not valid SAS names so I have no idea how you've named them*/

if n(of dates(*)) < 6 then delete;

PS. Observations are rows, variables are columns, variable values are the elements. Although I managed to figure out what you meant, your terminology is non-standard that others may not be able to follow.

@Amalik wrote:

For example, I have the following dataset,

Fundid

200801

200802

200803

200804

200805

200806

200807

200808

1

3

4 5

6 7

8

8

2

56

23

6

3

4

56

3

65

87

3

6

8

23

4

5

I would like to keep those fundid's with atleast 6 observations and delete those with less than 6 observations. So I would want the output to look like this,

Fundid

200801

200802

200803

200804

200805

200806

200807

200808

2

56

23

6

3

4

56

3

65

87

3

6

8

23

Re: delete rows

PGStats — Fri, 13 Apr 2018 03:50:02 GMT

Count the number of non-missing numeric variables :

data want;
set have;
if n(of _numeric_) >= 61; /* Including FundId, if FundId is numeric */
run;