Hold the date in name of colum while importing the xlsx file

[SASdevAnneMarie]

Hello Experts,

I can't hold the date in the name of columns, while proc import.

I tried also the libname libname xl XLSX but I have the same results :

My data is :

Thank you for your help !

[andreas_lds]

Try getnames='no' in proc import.

[SASdevAnneMarie]

Thank you Andreas !
It doesn't work, because I want to get the column names.
I added an image to my message.

[ballardw]

If you want a variable name like 11/01/2016 you will have to set the Option Validvarname=any to allow use of non-standard variable names. Then every place you use the variable name you must place the value in quotes and follow with an n. i.e. '11/01/2016'n .

You also have to make sure the cell is formatted properly.

And maybe fight with that and may require doing a Save AS to a CSV format and read that file.

Proc Import and Excel frequently have issues because Excel does not impose any rules on cell types. So in this case a numeric value such as a date is not readily recognized as a header and the numeric value gets used.

"Dates" are poor variable names because they hold actual data and different data structures are often much more versatile where your "date" becomes another variable with an actual date value.

[SASdevAnneMarie]

Thank you, Ballardw
It's work well, but I don't have the decimal in the amount (I have it in csv file).
How to despaly it please ?

[ballardw]

Display appearance of values is based on the Format assigned to a variable.

You would have to show us the current format assigned and what you expect the appearance to be to make a recommendation.

There are also national language settings that may be involved as part of the world uses "." for decimals and part uses "," . We would need to know which you expect.

[Kurt_Bremser]

See this short code example that transposes the mostly useless wide dataset to a much more useful long dataset, and converts the raw Excel date values to SAS dates:

data have;
input id $ '44355'n '44356'n '44357'n;
datalines;
A 1 2 3
B 4 5 6
;

proc transpose
  data=have
  out=long (rename=(col1=value))
;
var _numeric_;
by id;
run;

data want;
retain id date value; /* just for variable order */
set long;
date = input (_name_,5.) + '30dec1899'd;
format date yymmdd10.;
drop _name_;
run;

From the layout of your spreadsheet, I recommend you split it into two datasets, one with the base data (up to column "domicile"), and another with the time series and only the ISIN as key. Make sure that ISIN is stored as character, BTW.

And while you do that, also get rid of the non-valid column names in the base dataset, especially those with the French characters.

[SASdevAnneMarie]

Thank you Kurt,
It's work well, but sometimes, after the using the proc import, I have the name of column as charactere variable ('44357' ) when I have the missing amounts.
My data for column names is like : '44355'n '44356'n '44357' '44358' '44362'n;
This column names don't appear when I use your solution.

I updated the image of my data in my message, sorry.

[Kurt_Bremser]

Wait, wait.

All the time we were talking about IMPORTING an Excel file, and now suddenly it's EXPORT?

Please clarify.

---

@SASdevAnneMarie wrote:

Thank you Kurt,
It's work well, but sometimes, after the using the proc export, I have the name of column as charactere variable ('44357' ) when I have the missing amounts.
My data for column names is like : '44355'n '44356'n '44357' '44358' '44362'n;
This column names don't appear when I use your solution.

I updated the image of my data in my message, sorry.

---

[SASdevAnneMarie]

Sorry, "IMPORT", I will correct my message.

[Kurt_Bremser]

My "have" dataset has such column names, and the next two steps restructure the dataset and convert the names first to values and then the values to SAS dates.

"have" is a simplified form of the dataset you get when importing the spreadsheet into SAS. SAS will always use the raw values in the first row as column names, so your dates lose their formatting.

A number like 44355 is the internal Excel representation of a date, which is a count of days, starting with 1899-12-31 as day 1. Since SAS uses 1960-01-01 as day zero, we need to add the SAS value of 1899-12-30.

[Tom · Accepted solution]

You will probably have the fix the values after you have them in SAS.

The issue is that when SAS sees a DATE value in a character field (variable names/column headers are character strings) it returns the underlying number as a digit string.  The same thing happens when you have date values in a cells of columns that also contain other cells with character strings.

Another method is to read the data without the header row and then read the header row separately and use the information to rename the variables.

When you have a value like '42373' the way to convert it to a date is to convert the string to a number and then add the date '30DEC1899'd to the number to adjust the base date used in counting days.

So let's say you imported the Excel sheet into a dataset named HAVE.  You can get the names of the variables into data. Convert the "numbers" into "dates".  Then rename the variables.

proc transpose data=have(obs=0) out=names name=oldname;
  var _all_;
run;

data names;
  set names ;
  newname=oldname;
  number = input(oldname,??32.);
  if not missing(number) then newname=put(number+'30DEC1899'd,yymmdd10.);
run;

proc sql noprint;
  select catx('=',nliteral(oldname),nliteral(newname))
    into :renames separated by ' '
   from names
   where upcase(newname) ne upcase(oldname)
 ;
quit;

proc dataset nolist lib=work;
  modify have;
    rename &renames;
  run;
quit;

PS: Do not display dates in either MDY or DMY order.  It will confuse half of your audience.

[SASdevAnneMarie]

Thank you Tom !

I added : attrib newname format=$10. informat=$10. length=$10.;

I have an error : ERROR: Procedure DATASET not found. Could I replace the proc dataset ?

[Tom]

The procedure name is DATASETS with an S at the end. 

Note you can also include a rename statement in data step.  Or use the dataset option RENAME=.

data want;
  set have;
  rename &renames;
run;
data want2;
  set have(rename=(&renames));
run;

The length of a NAME (dataset name, variable name, catalog name, format name) in SAS is 32 bytes, not 10.

There is no need to attach the $ format or the $ informat to a character variables. SAS already knows how to display and read character variables so does not need any special instructions for them.

But note that the assignment statement:

newname=oldname;

will cause SAS to define NEWNAME as the same type and length as OLDNAME since it is the first place that NEWNAME is mentioned in the data step.

[SASdevAnneMarie]

Thank you, Tom !

When I don't use the attrib statement I have the truncated data like "2016-01-"