BookmarkSubscribeRSS Feed
itshere
Obsidian | Level 7

Hello  - 


I am uploading an CSV file like I always do in SAS. The file has 4 columns. One is a subscription number in text while the other ones are supposed to be in date format (mm//dd/yy hh:mm) 2 out of 3 are working while one of the columns is a single digit. when I go into properties it shows that 2 of the working columns are in number format while the not working one is in text. 

 

Why does this happen? I went back into the excel sheet the one i uploaded and all 3 columns are the same format (correct format) but when i upload it in SAS one of them changes to text.

 

Please help,

Thank you 

7 REPLIES 7
Kurt_Bremser
Super User

Inspect the CSV with a text editor, if that does not reveal anything, use a hex viewing tool.

 

PS If you used PROC IMPORT, extract the automatically created DATA step from the log and modify it to suit your requirements.

itshere
Obsidian | Level 7
is there something i can do in excel to change the format and import the file again? why is it that some columns worked and others didnt?
Patrick
Opal | Level 21

I assume you're either using Proc Import or the EG import wizard. With both these methods SAS analysis first the source data to derive an appropriate informat and format for reading and writing the text in the .csv.

 

Excel formats on cell level, SAS formats on column (variable) level. If you have in one of the "cells" in the .csv a character which can't be read as a date then SAS will chose a character informat and the resulting SAS variable will be character.

 

The "nasty" thing: Such a "hurting" character can be a special character which doesn't print and you don't see anything special when opening with Excel.

 

If the .csv is not too big then I would open the file with Notepad++ https://notepad-plus-plus.org/download/v6.8.3.html and under "View/Show Symbol" select "Show All Characters". This will show you everything that's in your source .csv.

ballardw
Super User

You may try going into EXCEL, highlight the whole column and then format cells to the desired date format.Then export to CSV.

You can get text on export when some manual edits are made and Excel changes the cell behavior. Whether you get text or a numeric like 49234 seems like luck of the dice.

Tom
Super User Tom
Super User

Don't look at the CSV file using Excel.  Excel changes the values when you open a CSV file.

If you just have four columns then write your own data step to read it.

 

 

data want ;
   infile 'myfile.csv' dsd truncover firstobs=2 ;
   length id $20 date1-date3 8 ;
   informat date1-date3 anydtdtm. ;
   format date1-date3 datetime. ;
   input id date1-date3;
run;
itshere
Obsidian | Level 7
I will try using that SAS code. which parts would i need to rewrite? aside from the myfile.csv.

I get an error saying the physical file does not exist when i put in my own file name/location.
Tom
Super User Tom
Super User

If you are running SAS via Enterprise Guide or SAS/Studio such that the actual SAS code is running on a different machine than your PC then you need to use a path that SAS can access.

 

You can change the variable names and/or the informats and/or the formats. In reality you can change the whole program if you need to.  But reading a delimited file with only four columns is very easy.

 

The INFILE statement tells SAS where the the data is coming from and sets some options like DSD to tell it how to process it.

The LENGTH statement defined the variables. You can also use an ATTRIB statement.  Or you can let SAS guess based on how you first reference the name, but if you wanted SAS to guess then you could keep using PROC IMPORT.

The INFORMAT statement is useful when you want SAS to transform the characters in the input stream. So in your case you need some that will translate a stream of characters that look to human like a date time value into a number.  It is not useful to attach informats to most variables as SAS already knows how to read those.

The FORMAT statement is useful when you want SAS to transform a variable to characters in a particular way so that it is easier for humans to read. In your case you want to transform your datetime values into text strings that humans can interpret as date and time value.  It is not useful, and can cause strange behaviour down the line, to attach $xx formats to character variables. SAS already knows how to display character variables.  For numeric variables it will normally use BEST12. format to display if you do not attach a format, so in most cases you do not need to attach formats to numeric variables either.

The INPUT statement is just a list of the variables you want to read. Since you have a delimited input file just list them in the order that they are in the input stream.

 

 

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
  • 7 replies
  • 912 views
  • 0 likes
  • 5 in conversation