- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have a data set with missing value (character & numeric), all replaced by 'not available' and I would like remove it from the entire data set. Any idea on how can I achieve this?
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Details matter.
Example data so we can see some of what you actually have.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
How is "not available" getting in the results? That does not sound like a SAS default behavior so something is setting it. Perhaps you have a format assigning that text to display for missing values?
And what code are you using to display the values?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I actually want to remove the 'not available' in the data set as seen on the attachment.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It does not appear to me that you actually have both character and numeric data. All the columns, even the putatively numeric columns are left justified (i.e. they are numeric characters, but not numeric values). I suggest this because it looks like you might be displaying a dataset with the sas studio viewer, which would display true numeric values right-justified.
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Mariloud - Your screenshot suggests that all the columns in your data are of type character despite the fact that some rows contain only numbers. A simple WHERE statement should remove these:
data want;
set have;
where MyVarName ne 'Not Available';
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I did try this approach it worked on other variable except on the Denominator variable there was not change.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Photographs of the data does not really show what is in the data. Post samples of the actual values.
If you are unsure how to do this try just dumping the values to SAS log and copying and pasted the resulting TEXT (don't take pictures of the text) into the pop-up window you get when push the Insert Code icon, looks like {i}, in this forum's editor.
Something like this should work:
data _null_;
set have (obs=5);
format _character_ $quote. ;
put (_all_) (=);
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sometimes you may have a leading blank in the value and " Not Available" would not be equal to "Not Available".
If strip(denominator) = 'Not Available';
might address that issue.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You need to explain the circumstances of when this occurs, not just the symptom. I've seen this happen when viewing a table in Enterprise Guide when there has been a glitch reading the underlying data. In my case simply closing and re-opening the table fixes the problem.