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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

8 REPLIES 8
Reeza
Super User

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

 


 

briannalindsay
Calcite | Level 5

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?

 

 

Reeza
Super User

How are you exporting your data?

 

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

briannalindsay
Calcite | Level 5

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 .

 

Reeza
Super User

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;

briannalindsay
Calcite | Level 5

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

Reeza
Super User

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

 

 

Patrick
Opal | Level 21

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1993 views
  • 0 likes
  • 3 in conversation