topic Creating Both Blank and Missing Values in a Data Set in SAS Programming

Creating Both Blank and Missing Values in a Data Set

AggieGal — Tue, 11 Dec 2018 20:10:21 GMT

Hello,

I need to create a dummy data set that contains both missing/null values and blank values.

Does anyone know how to do this? I need for someone to identify both as separate entities and act accordingly.

The main database has values that SAS reads as BLANKS and MISSING/NULL separately.

I need XYZ_Group 16 to be missing/null and XYZ_Group 7 to be read as blanks.

When I created the dataset both as a sas7bdat set and txt both values are reading as blank.

Thanks,

Re: Creating Both Blank and Missing Values in a Data Set

Reeza — Tue, 11 Dec 2018 21:05:11 GMT

Create a custom informat to read the data propery. Can you post the code you're using to read this file as well? And SAS doesn't really have a difference between blank and Null. So it depends on what you want to do with those values. It does have SAS special missing values, so you can assign .N to records that are not applicable and just . to variables that are null?

I think we need to understand more what you want to see as output to help you out.

@AggieGal wrote:

Hello,

I need to create a dummy data set that contains both missing/null values and blank values.

Does anyone know how to do this? I need for someone to identify both as separate entities and act accordingly.

The main database has values that SAS reads as BLANKS and MISSING/NULL separately.

I need XYZ_Group 16 to be missing/null and XYZ_Group 7 to be read as blanks.

When I created the dataset both as a sas7bdat set and txt both values are reading as blank.

Thanks,

Re: Creating Both Blank and Missing Values in a Data Set

AggieGal — Tue, 11 Dec 2018 20:47:24 GMT

I need XYZ_Group = 7 to be read as BLANK

while XYZ_Group = 16 to be Missing/Null

Proc Import datafile="<input>\SAS_Dummy_AggieGal_3.xlsx"
out=SAS_Dummy_XLSX
dbms=xlsx
replace;
run;

Proc Sql;
create table SAS_Dummy_Data as
select distinct
t1.ID,
t1.XYZ_Group,
t1.XYZ_Ind,
t1.XYZ_Ind_Desc,
(Case
when t1.XYZ_Ind is missing then "Missing/Null"
when t1.XYZ_Ind = " " then "Blank"
else t1.XYZ_Ind
end) as XYZ_Ind_Validation
from SAS_Dummy_xlsx as t1
order by t1.XYZ_Group,
t1.ID;
quit;

Re: Creating Both Blank and Missing Values in a Data Set

Reeza — Tue, 11 Dec 2018 20:50:53 GMT

If your source file is originally txt why did you switch to XLSX? It's actually harder to work with an XLSX file. Which type are you working with, because the approach I was suggesting will not work with an XLSX file.

Re: Creating Both Blank and Missing Values in a Data Set

AggieGal — Tue, 11 Dec 2018 20:54:09 GMT

i've attached a txt file

Re: Creating Both Blank and Missing Values in a Data Set

Reeza — Tue, 11 Dec 2018 21:07:04 GMT

You never actually answered the questions, specifically:

1. What do you want to see as the output?

2. What is the type/format of your input file, text or XLSX?

Assuming that you want SAS special missing values as the format this is relatively straightforward.

proc format;
invalue $ missing_fmt
'Missing/Null' = .M
'Blank' = .B
'' = .N
other = [$30.];
run;

data have;
informat ID xyz_group xyz_ind_desc xyz_ind $missing_fmt.;
input id $  xyz_group $ xyz_ind_desc $ xyz_ind $ ;
infile cards dlm=',' truncover dsd;
cards;
16_61,16,Missing/Null
16_62,16,Missing/Null
7_44,7,Blank,
7_45,7,Blank,
9_8,9,H,H
9_9,9,H,H
;;;;
run;

proc print data=have;
run;

This should get you started and you should be able to modify it to what you want to see.

Re: Creating Both Blank and Missing Values in a Data Set

AggieGal — Tue, 11 Dec 2018 21:19:42 GMT

My output dataset in SAS needs to show which variables are being read as blank and which are missing/null.

The live data contains both however after I move from the source data it becomes blanks.

I need the dataset to help with another problem I'm having but anyone who can help me obviously can't access my source data. So, I need dummy data to replicate my problem.

Re: Creating Both Blank and Missing Values in a Data Set

Reeza — Tue, 11 Dec 2018 21:38:50 GMT

@AggieGal wrote:

My output dataset in SAS needs to show which variables are being read as blank and which are missing/null.

The live data contains both however after I move from the source data it becomes blanks.

I need the dataset to help with another problem I'm having but anyone who can help me obviously can't access my source data. So, I need dummy data to replicate my problem.

I'm not sure what that means. Does the code work, if not, how does it not work?

Re: Creating Both Blank and Missing Values in a Data Set

AggieGal — Tue, 11 Dec 2018 21:41:42 GMT

I have access to live data sets that others don't. I need to create a separate data set that has the same quirks for teaching purposes:

Some values are being read from the source data as being MISSING/NULL

and others are being read as BLANK

However, every step and import data set the MISSING/NULL values are then being read as BLANK.