Variable is uninitialized

[docctong]

I am trying to import data into the SAS University Edition with the following code:

proc import datafile="/folders/myfolders//ABC-P WEEKLY CLAIMS REPORT.XLSX"
OUT=WORK.ABC1
DBMS=xlsx
Replace;
options MSGLEVEL=I;
*range="Sheet1$";
*Getnames=yes;
*Mixed=yes;
*Scantext=yes;
*Usedate=yes;
*Scantime=yes;
run;

options validvarname=any;
data work.abc1;
set work.abc1;
indemnityPDPaid = input(indemnity_PD_paid, dollar12.0);
MedicalBIPaid=input(medical_BI_paid, dollar12.0);
format indemnityPDpaid MedicalBIPaid Total_outstanding net_incurred dollar12.0;
drop indemnity_PD_Paid Medical_BI_Paid;
run;

proc import datafile="/folders/myfolders/ABC-P WEEKLY CLOSED CLAIMS REPORT.XLSX"
OUT=WORK.ABC2
DBMS=xlsx
Replace;
options MSGLEVEL=I;
*range="Sheet1$";
*Getnames=yes;
*Mixed=yes;
*Scantext=yes;
*Usedate=yes;
*Scantime=yes;
run;

options validvarname=any;
data work.abc2;
set work.abc2;
indemnityPDPaid = input(indemnity_PD_paid, dollar12.0);
MedicalBIPaid=input(medical_BI_paid, dollar12.0);
format indemnityPDpaid MedicalBIPaid net_incurred dollar12.0;
drop indemnity_PD_Paid Medical_BI_Paid;
run;

As you can see the two sets of codes are almost identical.  However, although I have no problem getting the "indemnityPDPaid" and "MedicalBIPaid" in the first dataset, i.e., work.abc1, I got error messages telling me that Variable indemnity_PD_paid is uninitialized and Variable medical_BI_paid is uninitialized.  Any clue?  

Please help.  Thanks.  

[PaigeMiller]

We don't have your data.

But nevertheless, when you get the message that the variable is uninitialized, you need to look at your data with your own eyeballs, using PROC PRINT or viewtable, or any other way to display the data its. When you look at work.abc1 and work.abc2 AFTER PROC IMPORT (not after the DATA step) in this manner, the problem will be obvious.

[docctong]

Thanks for the suggestion.  I'll try and hopefully will find what the problem is.

[ballardw]

Welcome to the wonderful world of relying on guessing. Proc Import has to guess as to variable information. Depending on how consistent whoever creates those Excel files the column headings may change slightly which means that the variable names change. As well as the data types. Note that the paths are different in the Proc Import steps. Are they supposed to be to the same Excel file or two different ones with the same name. If the later then the content issues come into play.

proc import datafile="/folders/myfolders//ABC-P WEEKLY CLAIMS REPORT.XLSX"

                                                               ^second / not in below

proc import datafile="/folders/myfolders/ABC-P WEEKLY CLOSED CLAIMS REPORT.XLSX"

I suspect that if you rerun the import for that set again and run proc contents on the set or investigate the variables with other tools that you will find those variables mentions as "uninitialized" do not exist in the data. Possibly something with similar spelling is there but computer programs are picky.

Because you used the construct:

Data work.abc2;

   set work.abc2;

you have replaced your initial data set and cannot verify that the import included the problem variables, though likely they appear far to the right of the data next to the variables you were attempting to calculate.

[docctong]

Thanks for the help.  The mistake that you pointed out in the code in fact does not exist because I removed couple folders in the path to keep the business info confidential and I missed taking one "/" out.  Of course, I should have done better and thanks for pointing that out.

The thing is that I ran this code in PC SAS for many months and it never had any problem.  However, when I tried to run it in SAS Studio, it gave me this variable not initialized issue.  I am the one who created the data and so I know for sure that the fields exist in Excel and the names are identical in both ABC-1 and ABC-2 (one again, I changed the name of the data set in the code to keep the business info unrevealed).

Is there something different in the variable naming rules between PC SAS and SAS Studio?

Thank you. 

[PaigeMiller]

As stated above, there is something about how the data is being imported via PROC IMPORT that is different. You need to actually look at the results of PROC IMPORT and see what the difference is. You can do this via PROC PRINT or viewtable or any other way of looking at the data; this must be done immediately after PROC IMPORT and not after the data step.

Looking at your data is an excellent way to figure out what these problems are. It is a very good habit to get into.

[SuzanneDorinski]

Have you checked the setting for SAS variable name policy in SAS Studio?

The default in SAS Display Manager is V7, which means when you import a spreadsheet from Excel, column headings will be adjusted so that there are no blanks or other illegal characters in the variable name in SAS.  Blanks in column headings are replaced with underscores, so if the column heading in Excel is Medical BI paid, the variable name in the SAS data set will be Medical_BI_paid.  

I think the default in SAS Studio is ANY, which means that the variable name will be Medical BI paid, because the ANY variable name policy allows blanks and special characters.  

To check the validvarname= setting in SAS Studio, click on the More application options icon in the upper right corner, and then click on Preferences in the menu.

More application options icon is to the left of the question mark icon in SAS Studio

The variable name policy setting is in the General section of Preferences, as shown in the screen shot below.  I set it to V7, because that's the naming policy I'm used to in SAS Display Manager.

SAS variable name policy setting in SAS Studio is in the General section of Preferences

I see that you have an options statement in your code examples, but it's after the PROC IMPORT.

I created a small Excel spreadsheet to show the effect of the validvarname option.  

Example spreadsheet to show effect of validvarname option

In the code example below, the first PROC IMPORT uses validvarname=ANY.  The second PROC IMPORT uses validvarname=V7.

%let path=/folders/myfolders/ODS Excel examples;

options validvarname=any;

proc import datafile="&path/TEST claims data.xlsx"
            out=test_data_any_validvarname
            dbms=xlsx replace;
run;

proc print data=test_data_any_validvarname;
  title 'Data set immediately after PROC IMPORT';
  title2 'PROC IMPORT used validvarname=ANY policy';
run;

options validvarname=v7;

proc import datafile="&path/TEST claims data.xlsx"
            out=test_data_v7_validvarname
            dbms=xlsx replace;
run;

proc print data=test_data_v7_validvarname;
  title 'Data set immediately after PROC IMPORT';
  title2 'PROC IMPORT used validvarname=V7 policy';
run;

The screen shot below shows the differences in the variable names.

Output showing effect of validvarname= option