topic Re: script in New SAS User

script

Dumi1 — Mon, 02 Dec 2019 11:08:47 GMT

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?

Re: script

Kurt_Bremser — Mon, 02 Dec 2019 11:30:28 GMT

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.

Re: script

JosvanderVelden — Mon, 02 Dec 2019 12:02:11 GMT

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;

Re: script

ed_sas_member — Mon, 02 Dec 2019 12:27:02 GMT

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,

Re: script

Dumi1 — Wed, 04 Dec 2019 06:33:05 GMT

Thank you so much, this worked

Re: script

Dumi1 — Wed, 04 Dec 2019 06:34:19 GMT

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