topic Re: Import data from Excel and then converting into defined variable in SAS Programming

Import data from Excel and then converting into defined variable

ab12nov — Sun, 30 Jul 2017 17:51:44 GMT

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

Re: Import data from Excel and then converting into defined variable

Kurt_Bremser — Sun, 30 Jul 2017 18:01:00 GMT

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.

Re: Import data from Excel and then converting into defined variable

Reeza — Sun, 30 Jul 2017 18:03:14 GMT

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

Re: Import data from Excel and then converting into defined variable

ab12nov — Sun, 30 Jul 2017 18:17:32 GMT

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.

Re: Import data from Excel and then converting into defined variable

Reeza — Sun, 30 Jul 2017 18:19:27 GMT

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 :).

Re: Import data from Excel and then converting into defined variable

ab12nov — Sun, 30 Jul 2017 18:32:02 GMT

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