topic Re: something not working right importing .xlxs file in SAS OnDemand Academics in SAS Software for Learning Community

something not working right importing .xlxs file in SAS OnDemand Academics

anamaria2 — Tue, 02 Nov 2021 23:28:16 GMT

Iam reading an excel file using OnDemand. I tried both my own code and the right clicking option. Some of the variables names have spaces/#/- characters in the names, which we would expect SAS to convert to underscore.

PROC IMPORT DATAFILE="/home/....../raw data.xlsx"
OUT=WORK.MYdata
DBMS=XLSX
REPLACE;
GETNAMES=YES;
RUN;

The log says everything went ok:

NOTE: The import data set has 75 observations and 31 variables.

NOTE: WORK.MYdata data set was successfully created.

However, this is how PROC CONTENTS looks like:

Group	Num	8	BEST.	Group
Plate	Num	8	BEST.	Plate
Cohort	Num	8	BEST.	Cohort
Dam ID	Num	8	BEST.	Dam ID
Infant ID	Num	8	BEST.	Infant ID
Day	Num	8	BEST.	Day
GM-CSF	Num	8	BEST.	GM-CSF
IFN-g	Num	8	BEST.	IFN-g
IL-1b	Num	8	BEST.	IL-1b
IL-ra	Num	8	BEST.	IL-ra

I did not get any of the usual messages about SAS converting IFN-g to IFN_g, etc...
Also running the exact same code in the licensed version produces this (which is what we would expect):

Group	Num	8	BEST.	Group
Plate	Num	8	BEST.	Plate
Cohort	Num	8	BEST.	Cohort
Dam_ID	Num	8	BEST.	Dam ID
Infant_ID	Num	8	BEST.	Infant ID
Day	Num	8	BEST.	Day
GM_CSF	Num	8	BEST.	GM-CSF
IFN_g	Num	8	BEST.	IFN-g
IL_1b	Num	8	BEST.	IL-1b
IL_ra	Num	8	BEST.	IL-ra

I an not sure what to try anymore. Why isn't SAS OnDemand giving me exactly the same result as the licensed version? (I ended up creating a SAS file in the licensed version and reading that file in the OnDemand, but I would like to be able to just import the xlsx file). Does anybody have any suggestions? I am attaching an abbreviated data set.Thank you!

Re: something not working right importing .xlxs file in SAS OnDemand Academics

Reeza — Tue, 02 Nov 2021 23:38:28 GMT

OPTIONS VALIDVARNAME=V7;

The default in Studio and EG is VALIDVARNAME=ANY which is a bit more modern but a pain to deal with when programming.
Add the option to your AUTOEXEC in Studio and forget about it 🙂

Re: something not working right importing .xlxs file in SAS OnDemand Academics

tom_grant — Wed, 03 Nov 2021 13:48:45 GMT

You can also set this option by select the More Options Icon on top right & then selecting preferences, select Tables from the list & set SAS variable name policy to V7.

Re: something not working right importing .xlxs file in SAS OnDemand Academics

tom_grant — Wed, 03 Nov 2021 13:50:18 GMT

Make sure you Save your preferences.

Re: something not working right importing .xlxs file in SAS OnDemand Academics

anamaria2 — Sun, 14 Nov 2021 06:32:55 GMT

Thank you so much for your help.

Worked perfectly.

ana-maria