BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yogi1
Calcite | Level 5

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!!!!!!!!!!!!!!!!!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
kannand
Lapis Lazuli | Level 10

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

kannand
Lapis Lazuli | Level 10

Great insight. Thanks for your input RW9

Kannan Deivasigamani
Yogi1
Calcite | Level 5

Thankyou So much.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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