BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sanjay1
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

20 REPLIES 20
RahulG
Barite | Level 11

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

sanjay1
Obsidian | Level 7

Yes Exactly

Reeza
Super User

@sanjay1 wrote:

Yes Exactly


Your code already does this ...

Loko
Barite | Level 11

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;

sanjay1
Obsidian | Level 7

Hi Loko

 

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

Reeza
Super User

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

Reeza
Super User

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

sanjay1
Obsidian | Level 7

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.

Reeza
Super User

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

Shmuel
Garnet | Level 18

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 ?

RahulG
Barite | Level 11
%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.

sanjay1
Obsidian | Level 7

Hi Rahul,

 

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

 

error_prone
Barite | Level 11

Still not clear how the result should look like.

 

sanjay1
Obsidian | Level 7

I just want to delete the . values from my variables

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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