DATA Step, Macro, Functions and more

SAS Date code

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

SAS Date code

Hi

 

I am unable to get the proper output. My data is in csv format ....

 

options yearcutoff=1920 pagesize=60 linesize=80 pageno=1;
libname vicone '/folders/myfolders/';
data vicone.tourdates;
length City $ 11 ;
infile '/folders/myfolders/Working with dates/tourdates.csv' dlm=',' dsd firstobs=2;
input City $ DepartureDate date11. Nights;
run;
proc print data=vicone.tourdates;
title 'Tour Departure Dates as SAS Date Values';
run;

 

please help me out!!!!!!!!!!!!!!!!!


Accepted Solutions
Solution
‎10-23-2015 11:48 AM
Super User
Super User
Posts: 7,997

Re: SAS Date code

You are using CSV engine in your code, however the file is XLSX.  As a recommendation, due to Excel being unstructured and unsuitable as a data capture or transfer format, I would suggest that you first save the data to CSV - do this by going to File->SaveAs, and choose CSV from the file options (or better yet, get the author of the data to use a proper system in the first place).  AS you are going from Excel to another format, you will then need to check the data is correct, Excel can have different formats, types, special characters etc. in each cell (hence why it is not a good format).  Once the data is in a proper data transfer format then its simply a matter of writing a datastep import program (not proc import which is a guessing function - you know your data, you specify it):

data want;

  infile "...your_file.csv" dlm=",";

  informat ....;

  format ...;

  length ...;

  label ...;

  input ...;

run;

View solution in original post


All Replies
Regular Contributor
Posts: 161

Re: SAS Date code

Hello - Please try this.....

 

proc import datafile="/folders/myfolders/data/tourdates"
out=work.in1
DBMS=xlsx
;
run;
proc print data=work.in1;

 

printed this o/p 

 

 

Obs Country DepartureDate Nights
1 New Zealand 03FEB2001 16
2 japan 13MAY2000 8
3 Greece 17OCT1999 12
4 Brazil 28FEB2001 8
5 Venezuela 10NOV2000 9
6 Italy 25APR2001 8
7 Russia 03JUN1997 14
8 Switzerland 14JAN2001 9
9 Austarlia 24OCT1998 12
10 Ireland 27AUG2000 7

 

Hope this helps. Good luck...!!!

Kannan Deivasigamani
Solution
‎10-23-2015 11:48 AM
Super User
Super User
Posts: 7,997

Re: SAS Date code

You are using CSV engine in your code, however the file is XLSX.  As a recommendation, due to Excel being unstructured and unsuitable as a data capture or transfer format, I would suggest that you first save the data to CSV - do this by going to File->SaveAs, and choose CSV from the file options (or better yet, get the author of the data to use a proper system in the first place).  AS you are going from Excel to another format, you will then need to check the data is correct, Excel can have different formats, types, special characters etc. in each cell (hence why it is not a good format).  Once the data is in a proper data transfer format then its simply a matter of writing a datastep import program (not proc import which is a guessing function - you know your data, you specify it):

data want;

  infile "...your_file.csv" dlm=",";

  informat ....;

  format ...;

  length ...;

  label ...;

  input ...;

run;

Regular Contributor
Posts: 161

Re: SAS Date code

Great insight. Thanks for your input RW9

Kannan Deivasigamani
New Contributor
Posts: 2

Re: SAS Date code

Thankyou So much.

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 325 views
  • 2 likes
  • 3 in conversation