topic Re: Removing special missings when exporting to a csv in SAS Procedures

Removing special missings when exporting to a csv

briannalindsay — Tue, 10 Oct 2017 16:24:02 GMT

Hi, I have a large dataset with over a thousand columns. All are coded with some special missing characters. Is there any way to export this to a .csv without retaining the special missings? I want to only have one type of missing data because this is causing my csv to have unique values like this:

Instead I want only unique values:

Please help.

Thanks!

-Brianna

Re: Removing special missings when exporting to a csv

Reeza — Tue, 10 Oct 2017 16:31:45 GMT

So you want to exclude all missing data?

@briannalindsay wrote:

Hi, I have a large dataset with over a thousand columns. All are coded with some special missing characters. Is there any way to export this to a .csv without retaining the special missings? I want to only have one type of missing data because this is causing my csv to have unique values like this:

1

0

A

T

F

Instead I want only unique values:

1

0

Please help.

Thanks!

-Brianna

Re: Removing special missings when exporting to a csv

briannalindsay — Tue, 10 Oct 2017 16:34:45 GMT

No, I want all missing data (including special missing) to be exported as missing rather than coded as 'A', 'F', 'T' etc. The program that I'm using doesn't recognize special missings therefore I have many columns that have random single character responses.

Is the only way to do this to recode before I export?

Re: Removing special missings when exporting to a csv

Reeza — Tue, 10 Oct 2017 16:35:42 GMT

How are you exporting your data?

Can you create a replication using the CLASS or CARS data set?

Re: Removing special missings when exporting to a csv

briannalindsay — Tue, 10 Oct 2017 16:44:30 GMT

I have tried exporting both by proc export and by ods.

In SAS the data would look like this:

data test;
input var1 $ var2 $ var3 ;
datalines;
a a _a
b b _b
c c _c
run;

When I export to csv it would look like this:

var1 var2 var3

a a A

b b B

c c C

but I want it to look like this:

var1 var2 var3

a a .

b b .

c c .

Re: Removing special missings when exporting to a csv

Reeza — Tue, 10 Oct 2017 17:00:47 GMT

You could try a custom format.

options missing='.';
proc format;
value num_custom_fmt
., .A-.Z = .
other = [best12.];
run;

proc print data=test;
format var3 num_custom_fmt.;
run;

Re: Removing special missings when exporting to a csv

briannalindsay — Tue, 10 Oct 2017 17:24:30 GMT

Would this work for all columns or would I have to do each column one by one?

Re: Removing special missings when exporting to a csv

Reeza — Tue, 10 Oct 2017 17:32:38 GMT

You have to apply to each column so if you have comma or currency formats it can be a pain.

You can use variable lists at least:

_numeric_ -> all numeric variables

var1 -- var100 -> all variables that are listed between var1 and var100

var1-var100 all variables indexed with var1-var100

Re: Removing special missings when exporting to a csv

Patrick — Tue, 10 Oct 2017 23:54:25 GMT

@briannalindsay

You could also create an intermediary dataset for export where you convert all the special missings to standard missings as done below:

data period_a;
  missing X I;
  input Id $4. Foodpr1 Foodpr2 Foodpr3 Coffeem1 Coffeem2;
  datalines;
1001 115 45 65 I 78
1002 86 27 55 72 86
1004 93 52 X 76 88
1015 73 35 43 112 108
1027 101 127 39 76 79
;
run;

options missing=' ';
data ds_for_export;
  set period_a;
  array nums _numeric_;
  do over nums;
    if missing(nums) then call missing(nums);
  end;
run;

options missing=' '; is used to then print/write the standard missings as blanks and not as dots.