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?
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;
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;
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,
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.