DATA Step, Macro, Functions and more

Import data from Excel and then converting into defined variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Import data from Excel and then converting into defined variable

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


Accepted Solutions
Solution
‎07-30-2017 02:13 PM
Super User
Posts: 19,875

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

[ Edited ]

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


 

View solution in original post


All Replies
Super User
Posts: 7,866

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 19

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

Posted in reply to KurtBremser

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.

Super User
Posts: 19,875

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

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 Smiley Happy.

 

Occasional Contributor
Posts: 19

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

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

Solution
‎07-30-2017 02:13 PM
Super User
Posts: 19,875

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

[ Edited ]

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


 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 184 views
  • 0 likes
  • 3 in conversation