Hi:
1) You could be talking about the PUT and INPUT Statements or the PUT and INPUT functions? There's a difference between the two. Generally, when you're talking about the STATEMENTS, PUT is used for WRITING information or data and INPUT is used for READING information or data (usually from a "flat" or ASCII text file).
The PUT FUNCTION is used to convert a number to a character string by applying either a SAS format or a user-defined format. The PUT function can also be used to create a new variable by converting one character string to another character string. The INPUT function is used to create a new variable by turning a character string or character variable into a number, by applying an INFORMAT.
2) The answer to this question depends on your data in the XLS file. For example, IF you have a column in the worksheet, let's say it's BIRTHDAY, and you format that column IN EXCEL as a DATE value, then SAS will read the BIRTHDAY column as a DATE value -- a number -- which contains a date value and then, yes, you can apply a format to that BIRTHDAY column in your PROC IMPORT step:
[pre]
PROC IMPORT OUT= WORK.mydata
DATAFILE= "c:\temp\test.xls"
DBMS=EXCEL2000 REPLACE;
SHEET="test$";
GETNAMES=YES;
format birthday mmddyy10.;
RUN;
proc contents data=work.mydata;
title 'look for format on BIRTHDAY column';
run;
proc print data=work.mydata;
title 'Now display BIRTHDAY without a format';
format birthday;
run;
[/pre]
For example, I made a mini worksheet with 3 columns, GRP, DATE and BIRTHDAY. The DATE column was DEFINED as a number with no decimals and was typed in the form 20080115 and the BIRTHDAY column was DEFINED in EXCEL as a DATE and displayed in the form 15-JAN-08.
When I read the worksheet into SAS using PROC IMPORT (in SAS 9.1.3), my PROC CONTENTS shows:
[pre]
# Variable Type Len Format Informat Label
2 date Num 8
3 birthday Num 8 MMDDYY10. DATE9. date2
1 grp Char 1 $1. $1. grp
[/pre]
Note that even though I named a column DATE, because I didn't tell Excel that it was a date variable there was NO way that SAS could figure out that it was a DATE variable. Date variables in both Excel and SAS are internally stored as the number of days from a "beginning date". For example, Excel's dates start on Jan 1, 1900 -- which was Day1, Jan 2, was Day 2, Jan 3 was Day 3, etc. In Excel, there are no dates before Jan 1, 1900 and if you have older dates, in the 1800s or earlier, then you have to store them as character strings or as a separate month, day and year columns. SAS has a 0 date -- Jan 1, 1960 and SAS dates can go foreward in time and backward in time. So -3334 is Nov 15, 1950 (a negative number because it was BEFORE Jan 1, 1960) and Nov 15, 2007 is the internal number: 17485 (a positive number because it was AFTER Jan 1, 1960).
So if you look at the report from PROC PRINT, you see that I have these 2 obs:
[pre]
Obs grp date birthday
1 a 20080115 17546
2 b 20071115 17485
[/pre]
without a format on BIRTHDAY, it is very easy to see that the numeric variable BIRTHDAY is internally stored as the number of days since Jan 1, 1960; while the numeric variable named DATE is stored as just big number.
Now, what if I want the DATE variable to be turned into SAS date variable -- the number of days since Jan 1, 1960???? That's where the PUT and INPUT functions will come in handy.
First, I would use the PUT function to turn the big number 20080115 into a character string. Then I would use the INPUT function to read the character string and convert it into a SAS date variable. (If the DATE column was already a character string, then I would not need the PUT function. But when I made my worksheet, I made DATE a number in Excel.)
This program shows a simple conversion:
[pre]
data mydata2;
set mydata;
charstring = put(date,8.);
newdate = input(charstring,yymmdd8.);
format birthday newdate;
run;
proc print data=mydata2;
title 'Both BIRTHDAY and NEWDATE show as internal number of days since Jan 1, 1960';
run;
proc print data=mydata2;
title 'BIRTHDAY and NEWDATE with Date FORMATS applied';
format birthday mmddyy10. newdate date9.;
run;
[/pre]
And the outputs from the 2 proc prints are shown at the end of the post.
You could use the SAS Libname Engine for Excel to read the worksheet and convert the DATE column in one pass through the data. For more help with this problem, you might consider contacting Tech Support.
For questions of this nature that are not related to SAS Clinical Trials directly, you might also consider posting in the future in some of the new forums. These new forums are intended for these types of questions -- such as the use of basic SAS procedures and the use of Macro Language elements and DATA step processing (which would include questions on the PUT or INPUT function and the conversion of numbers or character variables to dates).
cynthia
** The proc print results:
[pre]
Both BIRTHDAY and NEWDATE show as internal number of days since Jan 1, 1960
Obs grp date birthday charstring newdate
1 a 20080115 17546 20080115 17546
2 b 20071115 17485 20071115 17485
*********************************************************************************
BIRTHDAY and NEWDATE with Date FORMATS applied
Obs grp date birthday charstring newdate
1 a 20080115 01/15/2008 20080115 15JAN2008
2 b 20071115 11/15/2007 20071115 15NOV2007
[/pre]
