Help using Base SAS procedures

Removing special missings when exporting to a csv

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Removing special missings when exporting to a csv

[ Edited ]

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

 


Accepted Solutions
Solution
‎10-11-2017 03:15 PM
Respected Advisor
Posts: 4,736

Re: Removing special missings when exporting to a csv

[ Edited ]
Posted in reply to briannalindsay

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

View solution in original post


All Replies
Super User
Posts: 23,663

Re: Removing special missings when exporting to a csv

Posted in reply to briannalindsay

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

 


 

New Contributor
Posts: 4

Re: Removing special missings when exporting to a csv

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?

 

 

Super User
Posts: 23,663

Re: Removing special missings when exporting to a csv

Posted in reply to briannalindsay

How are you exporting your data?

 

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

New Contributor
Posts: 4

Re: Removing special missings when exporting to a csv

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 .

 

Super User
Posts: 23,663

Re: Removing special missings when exporting to a csv

Posted in reply to briannalindsay

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;

New Contributor
Posts: 4

Re: Removing special missings when exporting to a csv

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

Super User
Posts: 23,663

Re: Removing special missings when exporting to a csv

Posted in reply to briannalindsay

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

 

 

Solution
‎10-11-2017 03:15 PM
Respected Advisor
Posts: 4,736

Re: Removing special missings when exporting to a csv

[ Edited ]
Posted in reply to briannalindsay

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 163 views
  • 0 likes
  • 3 in conversation