topic Re: how to delete . values for multiple variables in SAS Programming

how to delete . values for multiple variables

sanjay1 — Tue, 06 Sep 2016 08:56:14 GMT

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;

Re: how to delete . values for multiple variables

RahulG — Tue, 06 Sep 2016 08:59:43 GMT

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

Re: how to delete . values for multiple variables

sanjay1 — Tue, 06 Sep 2016 09:26:25 GMT

Yes Exactly

Re: how to delete . values for multiple variables

Reeza — Tue, 06 Sep 2016 09:27:45 GMT

@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.

Re: how to delete . values for multiple variables

Reeza — Tue, 06 Sep 2016 09:28:35 GMT

@sanjay1 wrote:

Yes Exactly

Your code already does this ...

Re: how to delete . values for multiple variables

Loko — Tue, 06 Sep 2016 09:29:43 GMT

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;

Re: how to delete . values for multiple variables

RahulG — Tue, 06 Sep 2016 09:34:25 GMT

%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.

Re: how to delete . values for multiple variables

sanjay1 — Tue, 06 Sep 2016 09:44:29 GMT

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.

Re: how to delete . values for multiple variables

Reeza — Tue, 06 Sep 2016 09:52:41 GMT

@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.

Re: how to delete . values for multiple variables

sanjay1 — Tue, 06 Sep 2016 10:02:16 GMT

Hi Loko

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

Re: how to delete . values for multiple variables

Reeza — Tue, 06 Sep 2016 10:05:26 GMT

@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.

Re: how to delete . values for multiple variables

sanjay1 — Tue, 06 Sep 2016 10:54:16 GMT

Hi Rahul,

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

Re: how to delete . values for multiple variables

error_prone — Tue, 06 Sep 2016 11:20:11 GMT

Still not clear how the result should look like.

Re: how to delete . values for multiple variables

Shmuel — Tue, 06 Sep 2016 11:56:31 GMT

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 ?

Re: how to delete . values for multiple variables

sanjay1 — Tue, 06 Sep 2016 13:42:06 GMT

I just want to delete the . values from my variables

Re: how to delete . values for multiple variables

Reeza — Tue, 06 Sep 2016 13:57:45 GMT

@sanjay1 rather than us going around in circles please post your expected output.

Re: how to delete . values for multiple variables

Shmuel — Tue, 06 Sep 2016 14:15:37 GMT

You cannot delete a value of a variable !

You can: ignore missing values and depending on your calculation

you can replace missing value into 0 (zero) or into 1 (one):

if var = . then var = 0; /* to add or subtract */

once again, having input row as:

f13=2; f14=5; f15=8; f16=9;

what formula you use and what result do you expect ?

Re: how to delete . values for multiple variables

sanjay1 — Tue, 06 Sep 2016 15:49:51 GMT

Hi Reeza,

My concern is I dont want to get any missing values i.e dots in the variables.(dont want see the missing values I want to delete all the dots).

Re: how to delete . values for multiple variables

ballardw — Tue, 06 Sep 2016 20:32:22 GMT

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.

Re: how to delete . values for multiple variables

sanjay1 — Thu, 08 Sep 2016 07:59:48 GMT

Hi Reeza,

Thanks for your help