Import data from Excel and then converting into defined variable

[ab12nov]

Hi guys can you help me with the querry 

proc import datafile = 'C:\Documents and Settings\Administrator\Desktop\SAS\Excels_for_import\SASTopics_xls.xls'
out = SasTopic DBMS = excel;
range = "quick$";
run;

data SasUpdate;
set SasTopic;
Estimateddate = input(Date,date9.);
if Estimateddate < today() then
Status = 'Complete';
else 
Status = 'incomplete';
format Estimateddate date9.;
run;

The log window error : Invalid argument to function INPUT at line 164 column 17

The estimateddate variable is not getting formatted and today() function is returning date of 27SEP1997 not today's date.

Thanks in advance

[Kurt_Bremser]

First of all, the today() function WILL return today's date, unless your computer's hardware clock is set to a wrong date.

Inspect your date column in the imported dataset. I am very sure that it will not contain a date in the SAS date9 format, so you have to find the correct informat for your values.

[ab12nov]

I'm using VM.... but time there is set correctly... However the tab with internet time that was showing 25SEP2017.

I guess this is why SAS is unable to capture correct date. Thanks for heads up.

[Reeza]

Are you using a legitimate version of SAS. I've seen people use torrented versions which require setting back the date which would cause the issue you're seeing with date. 

There's a free version of SAS available for learning purposes. 

If you're using a legitimate version of SAS this comment does not apply :).

[ab12nov]

Hi Reeza,

As you said, while seeing the date variable in content it was already formatted, so didn't have to format it.

Secondly, I'm quite sure the product is legit. My organisation has provided me the software. It is the system issue. So I'll have to reach out Tech guys. Although I ask about the version of SAS that is legit or not. Thanks for the update.

Cheers,

Ayushmaan

[Reeza · Accepted solution]

Post a proc contents of your dataset after you import it, as well as your full log.

It's likely the date is being imported as a SAS data already. 

---

@ab12nov wrote:

Hi guys can you help me with the querry 

proc import datafile = 'C:\Documents and Settings\Administrator\Desktop\SAS\Excels_for_import\SASTopics_xls.xls'
out = SasTopic DBMS = excel;
range = "quick$";
run;

data SasUpdate;
set SasTopic;
Estimateddate = input(Date,date9.);
if Estimateddate < today() then
Status = 'Complete';
else 
Status = 'incomplete';
format Estimateddate date9.;
run;

The log window error : Invalid argument to function INPUT at line 164 column 17

The estimateddate variable is not getting formatted and today() function is returning date of 27SEP1997 not today's date.

Thanks in advance

---