topic Re: How to remove rows that have blank vaules in SAS Programming

How to remove rows that have blank values

ernie86 — Wed, 28 Aug 2024 02:06:20 GMT

I have the following data set, and I want to remove the rows that Q1_1345 to Q14b_6061 are all blanks. In this case, I want my output excludes the entire row of Program 302 and 402.

State

City

District

Program

Q1_1345

Q2_1112

Q3_1236

Q4_8626

Q5_1457

Q6_5656

Q7_8989

Q8_1568

Q9_8521

Q10_0123

Q11_2456

Q12_9630

Q13_2121

Q14a_6060

Q14b_6061

Anchorage

301

Yes

405

Yes

Sitka

302

Chicago

401

Yes

Mobile

262

Yes

Chicago

402

New York

998

Yes

Nampa

744

574

878

586

Yes

Charlotte

601

Yes

5852

748

Yes

Honolulu

101

Re: How to remove rows that have blank vaules

ballardw — Tue, 05 Sep 2017 21:24:10 GMT

Are the variables concerned numeric, character or a mix?

I ask because if they are all numeric then

if max(<put your variable list here>) = . then delete;

if mixed character then use

if nmiss(<list of the numeric variables>)= <the number of numeric variables> and cmiss(<list of character variables>) = <number of character variables> then delete.

Re: How to remove rows that have blank vaules

ernie86 — Tue, 05 Sep 2017 22:06:44 GMT

Thank you for the reply. It is a mix of numeric and character. Is there a better way of listing variables out? Because the dataset has about 200 variables, the table i posted is a shorten version.

Re: How to remove rows that have blank vaules

PGStats — Tue, 05 Sep 2017 22:21:14 GMT

Function cmiss accepts both numeric and character variables. So you can use

if cmiss(of Q1_1345 -- Q14b_6061) = <number of variables> then delete;

Note the double dash between the first and last variables in the list.

Re: How to remove rows that have blank vaules

ballardw — Tue, 05 Sep 2017 22:23:15 GMT

There are a number of ways of listing varaibles:

Q: (note the colon) would all variables that start with Q

Q13: all variables that start with Q13

if the Names are sequential and of the same type (all numeric or all character)

Q13245 - Q14456 would work,

two -- can be used to signify those that are adjacent left to right such as

Q15_23 -- Q27_abc

You can use mulitple groups of these in a single function call sucha as

Max(q13: , Q14_1 - Q15_6, Q27_abc -- Q39end)

When I looked at your Q1_1345 Q2_1112 Q3_1236 I did wonder what the _xxxx added to the variable name instead of Q1 Q2 Q3.

And of the variable you shows the only one that I would possibly have had as character was Q8. And if the range of values was small I may have had a custom informat/format pair to have that as numeric as well. I would say that for 99% of variable coded as Yes/No they should be read with an informat to assign 1/0.

Re: How to remove rows that have blank vaules

Oligolas — Wed, 06 Sep 2017 08:57:00 GMT

Hi,

it seems you have more Q variable to check than 'KeyVariables'

You could use this:

%MACRO DeleteEmptyObs(inputLib=,inputDs=,outputDs=,keyVars=);
   %let keyVars=%upcase(&keyVars.);
   PROC SQL noprint;
      SELECT 'missing('||strip(name)||')' into :MissingNames separated by ' AND '
      FROM sashelp.vcolumn
      WHERE libname eq upcase("&inputLib.") 
      AND memname eq upcase("&inputDS.")
      AND upcase(name) not in ("&keyVars.")
      ;
   QUIT;

   DATA &outputDs.;
      set &inputLib..&inputDS.;
      if &MissingNames. then delete;
   RUN;
%MEND DeleteEmptyObs;
%DeleteEmptyObs(inputLib=sashelp,inputDs=class,outputDs=work.test,keyVars=%str(name,age,sex));

Re: How to remove rows that have blank vaules

Ksharp — Wed, 06 Sep 2017 14:06:33 GMT

CODE NOT TESTED.


options missing=' ';
data want;
 set have;
if missing(cats(of Q1_1345 -- Q14b_6061 ) ) then delete;
run;

Re: How to remove rows that have blank vaules

GeethaMN — Mon, 30 Oct 2017 10:41:49 GMT

data del_mis;
set mis;
array allnum{*} _numeric_;
array allcar{*} _character_;
do i = 1 to dim(allnum);
if cmiss(allnum{i}) then delete;
end;
run;

Re: How to remove rows that have blank vaules

Sascoder — Mon, 30 Apr 2018 20:11:40 GMT

One more tip I didn't see listed in comments:

You need to know the order of variables in order to use the convenient

varnam1--varnamex method.

You can get that via

proc contents data =[dataset] position;

run;