script

[Dumi1]

Hi Guys

whats the script to change format of Year? I need it to be yyyymmdd on my excel sheet because currently its mmddyyyy. I tried changing it on excel but it doesn't change so I was wondering if I can import the excel in sas and change the format from there?

[Kurt_Bremser]

• Please use descriptive subject lines. "script" is NOT descriptive.
• Show us what your SAS data looks like, post it in a data step with data lines
• How data ends up in Excel is dependent upon how you export it to Excel, and upon settings there, especially the Windows locale.

[JosvanderVelden · Accepted solution]

You can use ODS as in the example below:

*%let path=/home/user/4tests/;
%let path=c:\temp\;

data sample(drop=_:);
  length cdate 8;
  _cdate=today();
  do _i=0 to 4;
    cdate=_cdate-100*_i;
    /* correct for the excel date */
    cdate + 21916;
    if _i=2 then call missing(cdate);
    output;
  end;
  stop;
run;

options missing=' ';
ods listing close;
ods excel file="&path.test.xlsx";
proc report data=sample;
  column cdate;
  define cdate/display 'Date Collected' format=yyyymmdd10. style(column)={cellwidth=1.5in tagattr='format: yyyymmdd'};
run;
ods excel close;
ods listing;

[Dumi1]

Thank you, I also tested this and it worked perfectly and it made me look very clever.

[ed_sas_member]

Hi @Dumi1,

In case in your Excel file, the date is recognized as a character expression (e.g. "12022019") then you can use a function like this:

=CONCATENATE(MID(A1;5;4);MID(A1;1;2);MID(A1;3;2))

It will return a character expression like "20191202".

MID() is equivalent to substr() in SAS.

Best,

[Dumi1]

Thank you so much, this worked