DATA Step, Macro, Functions and more

how to delete . values for multiple variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 114
Accepted Solution

how to delete . values for multiple variables

Hi Sas Experts,

 

I have 4 variables fy13,fy14,fy15,fy16 which have dot values.

I want to delete . values for multiple variables..

 

Below is my code;

 

data test;
set sample;
if fy13=. then delete;
if fy14=. then delete;
if fy15=. then delete;
if fy16=. then delete;
run;


Accepted Solutions
Solution
‎09-08-2016 09:49 AM
Super User
Posts: 10,490

Re: how to delete . values for multiple variables

options missing=" ";

will mean that you do not see a period for missing values if that is what you are attempting. SAS will have a value, missing or not, for every variable for every row.

View solution in original post


All Replies
Regular Contributor
Posts: 241

Re: how to delete . values for multiple variables

Do you want to delete the row if any of fy13,fy14,fy15,fy16 is having dot value?

Frequent Contributor
Posts: 114

Re: how to delete . values for multiple variables

Yes Exactly

Super User
Posts: 17,792

Re: how to delete . values for multiple variables


sanjay1 wrote:

Yes Exactly


Your code already does this ...

Super Contributor
Posts: 305

Re: how to delete . values for multiple variables

Than your code works.

 

you can write it in a more compact way using or:

 

data test;
set sample;
if missing(fy13) or missing(fy14) or  missing(fy15) or missing(fy16) then delete;
run;

Frequent Contributor
Posts: 114

Re: how to delete . values for multiple variables

Hi Loko

 

I Have used your code but am getting zero observations as result

Super User
Posts: 17,792

Re: how to delete . values for multiple variables


sanjay1 wrote:

Hi Loko

 

I Have used your code but am getting zero observations as result


Based on sample data you should have 1 observation. Please post your code and log. 

Super User
Posts: 17,792

Re: how to delete . values for multiple variables

@sanjay1 So what is your question? Given the problem and code I can't see what you're trying to accomplish. 

Please post more details and include an example of your data and what your expected output. 

Frequent Contributor
Posts: 114

Re: how to delete . values for multiple variables

Hi Reeza,

 

I want to calculate the % change for fy13-fy16/fy13
But since I have missing values am getting the result as missing.

 

Here is the code

data sample;
input fy13 fy14 fy15 fy16;
datalines;
. 15 . 16
19 . 2 15
. 22 8 .
17 18 18 20
24 28 . 9
;
run

I want to calculate the % change for fy13-fy16/fy13,
but since I have missing values am getting the result as missing.

Super User
Posts: 17,792

Re: how to delete . values for multiple variables


sanjay1 wrote:

Hi Reeza,

 

I want to calculate the % change for fy13-fy16/fy13
But since I have missing values am getting the result as missing.

 

Here is the code

data sample;
input fy13 fy14 fy15 fy16;
datalines;
. 15 . 16
19 . 2 15
. 22 8 .
17 18 18 20
24 28 . 9
;
run

I want to calculate the % change for fy13-fy16/fy13,
but since I have missing values am getting the result as missing.


That makes sense. You didn't indicate what you want as output though. 

Trusted Advisor
Posts: 1,372

Re: how to delete . values for multiple variables

The formula:  f13 - f16 / f13  - does not relate to f14 and f15 ?!

 

Do you mean: sum(of f13-f16) / f13 ? this formula will neglect missing values.

 

The only case, I see, you will get result of missing is when f13 is missing or

when all the 4 variables are missing.

 

Check yourself manually;

 

In the given sample, at least one row does not include any missing value:

17 18 18 20

What result do you expect to get for that row?

    17 - 20/13   or   (17-20) /13   or (17+18+18+20)/13 ?

Regular Contributor
Posts: 241

Re: how to delete . values for multiple variables

%let dsn=sashelp.cars;
proc contents data=&dsn. out=contents2(keep =name type) noprint;
run;

%macro delete_cond;

proc sql noprint;
select name into :_name separated by ' '
from contents2
where type =1; /* Check for numeric columns */
quit;

%let key_col_cnt=%sysfunc(countw(&_name));

%do i=1 %to &key_col_cnt;
%let col=%sysfunc(scan( &_name,&i));
%let str=%str(  if &col = . then delete ;);
%put &str;

%end;
%mend;

%delete_cond;
data new_cars;
set &dsn.;
%delete_cond;
run;

I have %let dsn=sashelp.cars; 

you need to update it.

Frequent Contributor
Posts: 114

Re: how to delete . values for multiple variables

Hi Rahul,

 

Am new to sas, Your code is confusing to me, if you dont mind could you please help me without macros

 

Contributor
Posts: 33

Re: how to delete . values for multiple variables

Still not clear how the result should look like.

 

Frequent Contributor
Posts: 114

Re: how to delete . values for multiple variables

I just want to delete the . values from my variables

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 525 views
  • 2 likes
  • 7 in conversation