converting mmddyy10. to Date9 format

[Lav001]

Hi All,

I have data in excel file, I used PROC IMPORT and created a SAS dataset "Test".

In this dataset, there is variable "expiration_date" in mmddyy10 format. I'm trying to convert the variable to date9 format.

I used the below code, but it is not changing to date9 format, still showing as mmddyy10. format, can you please let me know.

data test;

set test;

format expiration_date date9. ;

run;

[novinosrin]

is your expiration_date char or num

Have you verified with proc contents?

[Lav001]

Checked now, it is defined as a number.

[novinosrin]

Are you sure, can you post the log 

data t;
k=today();
format k mmddyy10.;
run;

data t ;
set t;
format k date9.;
run;

[Lav001]

I figured out the problem, the variable name in excel is "expiration date".

In the data step, I wrote the below code,

data test;

length expiration_date 8;

set test;

-----

----

label expiration_date  = "expiration date";

---

run;

And forgot to rename "expiration date" as expiration_date, 

so a new variable with expiration_date is created with missing values, and since I labeled  expiration_date as "expiration date"

I got confused and was checking the "expiration date" variable imported from excel (why it is not converted to date9. format).

Now I have renamed the variable and its working(converted to date9.)

Can you please let me know if there is another way to convert the variable names to SAS conventions other than using rename option, as I have a lot of variables.(tried using validvarname=ANY, but didn't worked)

[novinosrin]

Hi @Lav001   I vaguely remember that proc import autoconverts variable names when reading excel files i.e the embedded blanks by replacing with underscore. I avoid using proc import coz I prefer to control by reading with data step and not let proc import to guess. In my opinion proc import is an awful procedure especially for large and very large datasets and absolutely not advisable for production environment

[SuryaKiran]

• Set VALIDVARNAME=V7 instead of ANY.
• When you have blanks in your columns name then you have to determine as literal, i.e: format 'expiration date'n date9.;
• If you want to rename all the invalid names then try this:

data test;
input 'First #@$%Name'n $  'Last Name'n $ 'Date Today'n;
datalines;
A B 9
;
run;

proc sql;
select distinct catx('=',nliteral(name),translate(strip(name), repeat('_', 50), compress(strip(name),'_', 'adi')))
		into: rename separated by ' '
	from dictionary.columns
where memname='TEST';
quit;


data test;
set test(rename=(&rename));
run;

[ballardw · Accepted solution]

---

imported from excel (why it is not converted to date9. format).

Now I have renamed the variable and its working(converted to date9.)

Can you please let me know if there is another way to convert the variable names to SAS conventions other than using rename option, as I have a lot of variables.(tried using validvarname=ANY, but didn't worked)

---

Proc Import guesses at so many things, and often badly and especially from Excel, I don't use import except to provide a starting point with writing code to read a CSV or similar file. Save the Excel to CSV using the file  save as menu. Then use Proc import on the csv adding a GUESSINGROWS statement with a largish numeric value to use more lines of data to "guess" with. Default for Excel is 20 rows and requires changing the Windows registry to increase.

Proc Import reading a CSV or other delimited file will create data step code that will appear in the log. Copy from the log and paste into the editor and modify as desired (SAVE the code). Use search-and-replace to make nicer variable names, verify that the INFORMATS are correct for values (things like account or identification numbers often end up as numeric when they should be character, especially if there are leading significant 0s involved), check the lengths on character variables. Add Labels to hold the text of long original column headings if desired. If you have documentation as to the expected lengths of variables you can set that with informat or length statements before reading. Set desired formats as well.

If you are likely to have multiple files in the same format then next time just change the names of the input file and the output data set, This way if you need to combine the data sets from the same layout the variables with have the same type. A different sequence of data often causes Proc Import to change variable types, especially with Excel files.

[novinosrin]

Thank you for the super comprehensive explanation. That says it all!