Help using Base SAS procedures

Import Excel file with missing values

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Import Excel file with missing values

[ Edited ]

How to import excel file with missing value into sas.

The format for the missing value is looks like: 

Id  X  Y     Z

1   .   5      .

2  .    3      . 

3  .    1      .

4  a   2     3

5  b   7    4

                     

Thank you!                                 


Accepted Solutions
Solution
‎04-21-2018 07:29 PM
Valued Guide
Posts: 596

Re: lijuu

Missing characters in SAS are represented by blanks and missing numeric values are represented by a period ( . ) In the data you showed you have period as missing character. Make sure to change those values into blank after reading data into SAS using Proc Import because period for character is not treated as missing.

 

Convert characters into missing as  follows:

data want ;
set test;
array change_Char _Character_;
do over change_Char ;
if strip(change_Char)="." then change_Char=" ";
end;
run;

Also some numeric variables can be imported as character, using INPUT() you can convert them to numeric

 

Thanks,
Suryakiran

View solution in original post


All Replies
PROC Star
Posts: 8,164

Re: lijuu

The following works for me. All missing values come in as missing.

 

Art, CEO, AnalystFinder.com

Solution
‎04-21-2018 07:29 PM
Valued Guide
Posts: 596

Re: lijuu

Missing characters in SAS are represented by blanks and missing numeric values are represented by a period ( . ) In the data you showed you have period as missing character. Make sure to change those values into blank after reading data into SAS using Proc Import because period for character is not treated as missing.

 

Convert characters into missing as  follows:

data want ;
set test;
array change_Char _Character_;
do over change_Char ;
if strip(change_Char)="." then change_Char=" ";
end;
run;

Also some numeric variables can be imported as character, using INPUT() you can convert them to numeric

 

Thanks,
Suryakiran
Contributor
Posts: 22

Re: lijuu

Posted in reply to SuryaKiran
Thank you in advance!
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 400 views
  • 0 likes
  • 3 in conversation