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
... View more
