DATA Step, Macro, Functions and more

Facing problem in creating columns from CSV file

Reply
Occasional Contributor
Posts: 8

Facing problem in creating columns from CSV file

I have converted the CSV file into SAS but I am unable to find any solution for separating the columns. There are two variables one is the Date (DDMMYY) and the other is the Spot Prices of Oil. Please help

Super User
Posts: 19,822

Re: Facing problem in creating columns from CSV file

Posted in reply to saugatasantra84

Please post your code and log. 

 

If you can, include the sample file. 

 

Otherwise, search on here and you'll find multiple suggestions on how to import data - using PROC IMPORT is a quick way as well.

Occasional Contributor
Posts: 8

Re: Facing problem in creating columns from CSV file

SpotPricesSpotPrices

 The code that i had used to convert the csv file was

 

proc import datafile = "Path\daily-spot-prices-for-heating-oil-1986-march-2016.csv"

out = mylib.Spotprices

dbms = tab

replace;

run;

 

Super Contributor
Posts: 345

Re: Facing problem in creating columns from CSV file

Posted in reply to saugatasantra84

You are using the wrong delimiter in proc import, try:

proc import datafile = "Path\daily-spot-prices-for-heating-oil-1986-march-2016.csv"
out = mylib.Spotprices
dbms = dlm
replace;
delimiter=";";
run;

 

Occasional Contributor
Posts: 8

Re: Facing problem in creating columns from CSV file

Posted in reply to andreas_lds

Many thanks it is working but one problem i am finding is the last column where words have not come in full,

New York Harbor No. 2 Heating Oil Spot Price FOB (Dollars per Gallon) (this is the full label that should have come but instead it is displaying (New_York_Harbor_No__2_Heating_Oi), so what can i do to give a full label name? Please suggest with codes

Super User
Posts: 19,822

Re: Facing problem in creating columns from CSV file

Posted in reply to saugatasantra84

Post your code, we don't know what solution you're using to make further suggestions about why it's not working the way you want.

Occasional Contributor
Posts: 8

Re: Facing problem in creating columns from CSV file

proc import datafile = "E:\Analytics_Backup_Acche\daily-spot-prices-for-heating-oil-1986-march-2016.csv"
out = mylib.SpotPrices
dbms = dlm
replace;
delimiter = ";";
run;

 

this is running fine but the last column has text exceeding 32 characters hence it is not coming full, so what to do

Super User
Posts: 19,822

Re: Facing problem in creating columns from CSV file

Posted in reply to saugatasantra84

add the GUESSINGROWS option and set it to MAX

 

guessingrows = max;

The first observations must be shorter so the record gets truncated. It's best to use a data step in these cases (check your log) but setting GUESSINGROWS is another lazy option, but not recommended for long term use. 

Occasional Contributor
Posts: 8

Re: Facing problem in creating columns from CSV file

its not working
Super User
Posts: 19,822

Re: Facing problem in creating columns from CSV file

Posted in reply to saugatasantra84

saugatasantra84 wrote:
its not working

What does that mean?

Occasional Contributor
Posts: 8

Re: Facing problem in creating columns from CSV file

let me put things into perspective, I had imported a csv file now after importing it the last column heading label was incomplete (since it was exceeding 32 characters) so that why I was asking for help if any further code can be applied to increase the label??

Super User
Super User
Posts: 7,060

Re: Facing problem in creating columns from CSV file

[ Edited ]
Posted in reply to saugatasantra84

saugatasantra84 wrote:

let me put things into perspective, I had imported a csv file now after importing it the last column heading label was incomplete (since it was exceeding 32 characters) so that why I was asking for help if any further code can be applied to increase the label??


As I said before do not use PROC IMPORT to read a delimited file. Especially one with only four columns. You will end up writing more code than if you just wrote the data step to read the file.

 

PROC IMPORT apparently will NOT convert the raw column header into the label for the variable that it generates. But you could read the column headers from the file yourself and use them to generate LABEL statements.

 

Here is worked example, including creating the example CSV file to read.

* Make a test CSV file ;
filename csv temp;
data _null_;
 file csv dsd;
 put 'This column header is too long to use as a variable name'
   / '1' 
   / '2' 
 ;
run;
* Use PROC IMPORT to make a dataset;
proc import out=test replace datafile=csv dbms=csv ;
run;
* Get the names that PROC IMPORT created ;
proc transpose data=test(obs=0) out=names ;
  var _all_;
run;
* Read the column headers to use as labels ;
data labels ;
  set names(keep=_name_) ;
  infile csv obs=1 dsd truncover ;
  length _label_ $256 ;
  input _label_ @@ ;
run;
* Generate macro variable with name="label" pairs ;
proc sql noprint ;
%let labels=;
  select catx('=',_name_,quote(trim(_label_)))
    into :labels separated by ' '
    from labels
    where _name_ ne _label_
  ;
quit;
* Modify the dataset to have the labels ;
proc datasets nolist lib=work;
  modify test ;
  label &labels ;
  run;
quit;
* Show the definition ;
proc contents data=test;
run;
* Print the data with the labels for column headers ;
proc print data=test label;
run;

image.png

Super User
Posts: 19,822

Re: Facing problem in creating columns from CSV file

Posted in reply to saugatasantra84

The label should be correct by default. The variable name is limited to 32 characters and there's no way around that.

Occasional Contributor
Posts: 8

Re: Facing problem in creating columns from CSV file

We cannot increase it? or if we want to rename?
Super User
Super User
Posts: 7,060

Re: Facing problem in creating columns from CSV file

Posted in reply to saugatasantra84

saugatasantra84 wrote:
We cannot increase it? or if we want to rename?

You can use the LABEL statement to add a descriptive label for your variaibles.

You can use the RENAME statement to change the name of a variable.

 

Ask a Question
Discussion stats
  • 20 replies
  • 215 views
  • 0 likes
  • 5 in conversation