DATA Step, Macro, Functions and more

Columns in Text - HELP!

Reply
Contributor
Posts: 26

Columns in Text - HELP!

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 

Super User
Posts: 6,927

Re: Columns in Text - HELP!

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 26

Re: Columns in Text - HELP!

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?
Respected Advisor
Posts: 3,886

Re: Columns in Text - HELP!

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.

Super User
Posts: 10,460

Re: Columns in Text - HELP!

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.

Super User
Super User
Posts: 6,495

Re: Columns in Text - HELP!

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;
Contributor
Posts: 26

Re: Columns in Text - HELP!

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.
Super User
Super User
Posts: 6,495

Re: Columns in Text - HELP!

[ Edited ]

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.

 

 

Ask a Question
Discussion stats
  • 7 replies
  • 234 views
  • 0 likes
  • 5 in conversation