BookmarkSubscribeRSS Feed
YangYY
Quartz | Level 8
1AFAfghanistan0.2130.1880.09970.08910.080.07270.0660.05970.05520.04230.03850.0390.04870.05180.03940.05290.06370.08540.1540.2420.2940.4120.35
2ALAlbania1.681.310.7760.7320.6130.6720.6520.4990.5650.9580.9681.031.21.381.341.381.281.31.461.481.561.791.68
3DZAlgeria2.972.982.952.963.053.33.32.923.532.992.822.672.812.832.73.222.993.193.163.423.33.293.46
4ASAmerican Samoa.......................
5ADAndorra7.477.186.916.746.496.667.077.247.667.988.027.797.597.327.367.36.756.526.436.126.125.875.92
6AOAngola0.420.4050.4010.4310.2810.7690.7120.4890.4710.5740.580.5730.7210.4980.9960.981.11.21.181.231.241.251.33
Hi,
As you can see those are some of my output data. Now I want to delete all the rows which shows "." in the columns(row 4 is just one of them. There are more rows like row 4 need to be deleted.). Can anyone tell me how to do that?
Anything will be appreciated!!
 
Thank you!!
14 REPLIES 14
KachiM
Rhodochrosite | Level 12

@YangYY 

 

If your input data set is "HAVE" then use:

 

data want;
   set have;
   array k _numeric_;
   if dim(k) = n(of k[*]);
run;
YangYY
Quartz | Level 8

Hi,

Thank you for your reply.

I have tried your code and it works. However,the output data shows there are 152 rows but the given answer saids it should be 176 rows.

Do you have another solution that I can try? 

 

Thank you

KachiM
Rhodochrosite | Level 12

@YangYY 

 

If all Numeric variables in an observation are missing then use this:

 

data want;
   set have;
   array k _numeric_;
   if nmiss(of k[*]) = dim(k) then delete;
run;

 

YangYY
Quartz | Level 8
I tried this code but it doesn't work.
But thanks!
KachiM
Rhodochrosite | Level 12

@YangYY 

 

May be your specification is not understood. Let us work with an example.

 

data have;
input x :$8. y1 y2 y3;
datalines;
AAAAA 10 20 30
BBBBB 10 .  .
CCCCC  . .  .
;
run;

In this dataset, I understood that you want the first two rows only. So my code with suggestion made by @Astounding :

 

data want;
   set have;
   if nmiss(of _numeric_ ) = dim(k) then delete;
run;

produces the first two records. 

 

Can you explain your issue using this example?

 

 

YangYY
Quartz | Level 8
Yes. But the log shows error.
72 data combined;
73 set combined;
74 if nmiss(of _numeric_) = dim(k) then delete;
ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.
75 run;
KachiM
Rhodochrosite | Level 12

@YangYY 

 

The revised code was not tested. Here is the solution:

 

data want;
   set have;
   array k _numeric_;
   if nmiss(of _numeric_ ) =  dim(k) then delete;
run;

Also, the code shown by @Astounding can be adapted as:

 

data want;
   set have;
   if n(of _numeric_) NE 0;
run;

Hope this solves your difficulty. All the best.

YangYY
Quartz | Level 8
After trying both code, the output data shows 269 observations which is same as the original data. And the observation with all value missing are not deleted.
And I am sure I understand the data "want" and data"have".
Still thanks.
KachiM
Rhodochrosite | Level 12

@YangYY 

 

Both the programs work fine. I suspect your input dataset. Proc Print your dataset for visualization and check for any inconsistency, particularly for the DELIMITERs. 

 

I was curious and tried your example data set. It works fine. See the output:

 

Capture.JPG

 

 

YangYY
Quartz | Level 8

 

 

 

KachiM
Rhodochrosite | Level 12

@YangYY 

 

I tried again it still doesn't work. 

Can you please try to copy my code and use the data "combined" to try your code again and see if it work?

 

data world_attr;
set mapsgfk.world_attr;
run;

proc sort data=world_attr;
by IDNAME;
run;

data world_attr;
set world_attr;
rename IDNAME=country;
run;

data combined;
merge world_attr co2_emission;
by country;
run;

 

 

I looked at the data for 176 countries in the Excel sheet giving CO2 Emissions. I don't see world_attr data set. You have not told the location for that. 

Further, Excel sheet, I do not see "American Samoa" as you have given in your first post. 

 

I insist that you closely examine your input data and DO the homework before asking further questions. Show your log for all your steps. If it is voluminous use sample of 20 rows. I am not your programmer to do all.

 

Good luck for finding the solution.

your work. 

PeterClemmensen
Tourmaline | Level 20

Do you want to delete observations where all values are missing or observations where at least one value is missing?

YangYY
Quartz | Level 8
I want to delete the observations where all values are missing.
Thank you
Astounding
PROC Star
First, simplify the code. This comparison works but doesn't require arrays:

if n(of _numeric_)=0 ;

To find the 24 lost observations, you have to roll up your sleeves and examine the data. Perhaps there are some character variables to the right that need to be considered. Perhaps some numeric variables contain special missing values (such as .A or .B which are different from .) that should not be deleted. Or perhaps the answer you were given is wrong and 152 is correct.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 14 replies
  • 2288 views
  • 2 likes
  • 4 in conversation