Proc Import excel missing data

[HitmonTran]

Hello,

I did a proc import.

%let importds="&path.\Data\raw\Box\Cue_Combined MDF 2022 0909.xlsm";  

 
proc import datafile=&importds
 out=flu_covid_raw
 dbms=xlsx replace;
 getnames=no;
run;

And when looking at the SAS dataset it looks like it is "missing"; however, when I use a statement "if var1=' ' then var2='Missing';  It doesn't recognize the missing values.  I know this has to do with the excel file but how do I solve this problem in SAS? thank you

[Reeza]

---

@HitmonTran wrote:

And when looking at the SAS dataset it looks like it is "missing"; however, when I use a statement "if var1=' ' then var2='Missing';  It doesn't recognize the missing values.  I know this has to do with the excel file but how do I solve this problem in SAS? thank you

---

What is "it"? 

What happens if you use the MISSING() function?

if missing(var1) then var2 = 'Missing';

[ballardw]

All variables with missing data? Some variable missing for every record? Some variable missing for some records?

Please describe clearly.

One potential cause of having one or more variables will all values missing are "phantom" columns. Excel has an obnoxious habit of treating a column that ever had values entered in it as populated even if every single value in the column is deleted. Since your code did not bring in column headings as names this is harder to see from SAS. You need to open your Excel file and look at the column(s) to see if any values actually exist. Typically these are on the right hand side of the data but if there are hidden columns you may have this happen as well.

If the values are missing in a specific variable only sometimes the most likely cause is either a blank cell or incompatible data with the type of variable that SAS determined the column to contain. One example is if the first rows of the column contain things that look like they may be numeric, i.e. only digits and decimal points, SAS may have determined the data should be numeric so if some rows of data have a text value like "NA" or "less than 5" or "<5" then SAS sets those as missing as they are not valid numeric values.

Similarly if SAS determined a variable should be a Date type value and some row does not have a date, or something that can be treated as as a date, then it would be set to missing.

Depending on how you "look" at the SAS data set there is even a chance that a text variable has carriage return characters or many leading blanks and just does not "fit" the viewer with default settings.

To test if a numeric variable is missing you should use either (best): missing(variablename) or: variablename = .   When you use variablename=' ' it may work as desired but the log will show a message like:NOTE: Character values have been converted to numeric values at the places given by:

      (Line):(Column).

which means the value in the ' ' was converted to numeric and the result is not always as desired.

If Var1 is a character variable then what you "see" may be other characters than a simple space. It might hold 3 consecutive spaces which would not be equal to a single space used in comparison code. It might be Tab character, it may be something else that doesn't display.

The Missing(variablename) function will true for character variables as well as numeric. It treats multiple spaces as missing as well.

Hint: run proc Contents on your data set to see the variable types.