Facing problem in creating columns from CSV file

[saugatasantra84]

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

[Reeza]

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.

[saugatasantra84]

SpotPrices

 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;

[andreas_lds]

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;

[saugatasantra84]

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

[Reeza]

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.

[saugatasantra84]

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

[Reeza]

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. 

[saugatasantra84]

its not working

[Reeza]

---

@saugatasantra84 wrote:
its not working

---

What does that mean?

[saugatasantra84]

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

[Tom]

---

@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;

[Reeza]

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

[saugatasantra84]

We cannot increase it? or if we want to rename?

[Tom]

---

@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.