topic Re: Facing problem in creating columns from CSV file in SAS Programming

Facing problem in creating columns from CSV file

saugatasantra84 — Fri, 27 Oct 2017 20:13:21 GMT

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

Re: Facing problem in creating columns from CSV file

Reeza — Fri, 27 Oct 2017 21:50:52 GMT

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.

Re: Facing problem in creating columns from CSV file

ballardw — Fri, 27 Oct 2017 23:10:33 GMT

Separate what? Into what? Using what rules?

And did you read the data with a data step or import using a wizard or proc import code?

It may not hurt to post the result of proc contents here as well. That way we will know whether your variable(s) are numeric or character, which may not be obvious pasting values.

Re: Facing problem in creating columns from CSV file

saugatasantra84 — Sat, 28 Oct 2017 10:56:47 GMT

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;

Re: Facing problem in creating columns from CSV file

andreas_lds — Sat, 28 Oct 2017 13:58:30 GMT

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;

Re: Facing problem in creating columns from CSV file

Tom — Mon, 30 Oct 2017 18:49:48 GMT

Why would you ever use PROC IMPORT to read a file with 4 columns?

It is more work to write the PROC IMPORT code than to write the DATA step code.

With a DATA step you have complete control over how SAS is reading the data including what variable names to use and what labels to attach to the variables.

You data file has the date in three variables. You can combine them to create an actual date variable.

data want ;
  infile 'myfile.csv' dsd dlm=';' firstobs=2 truncover ;
  length Year 8 Month $20 Day 8 FOB 8 ;
  label fob='New York Harbor No. 2 Heating Oil Spot Price FOB (Dollars per Gallon)';
  input year month day fob ;
  date = input(cats(day,substr(month,1,3),year),date9.);
  format date date9.;
run;

Once you have actual date and value pairs you then use SAS procedures to analyze the data.

proc sort data=want; by date; run;
PROC SGPLOT DATA = Want;
 SERIES X = date Y = fob;
run;

Re: Facing problem in creating columns from CSV file

Reeza — Sat, 28 Oct 2017 17:37:14 GMT

DBMS =TAB means your delimiter is a tab.

Since your file is a CSV try DBMS=CSV

Re: Facing problem in creating columns from CSV file

saugatasantra84 — Mon, 30 Oct 2017 14:55:50 GMT

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

Re: Facing problem in creating columns from CSV file

Reeza — Mon, 30 Oct 2017 15:02:36 GMT

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.

Re: Facing problem in creating columns from CSV file

saugatasantra84 — Mon, 30 Oct 2017 15:24:40 GMT

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

Re: Facing problem in creating columns from CSV file

Reeza — Mon, 30 Oct 2017 15:26:04 GMT

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.

Re: Facing problem in creating columns from CSV file

Tom — Mon, 30 Oct 2017 15:37:40 GMT

@saugatasantra84 wrote:

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

Why would there be anything to do? You cannot have a variable name that is longer than 32 characters (and why in the world would you want one?). Run proc contents and you should see that the original column headers are used as the variable labels.

proc contents data=mylib.SpotPrices;
run;

proc print label data=mylib.SpotPrices;
run;

Re: Facing problem in creating columns from CSV file

saugatasantra84 — Mon, 30 Oct 2017 15:39:49 GMT

its not working

Re: Facing problem in creating columns from CSV file

Reeza — Mon, 30 Oct 2017 15:42:38 GMT

@saugatasantra84 wrote:
its not working

What does that mean?

Re: Facing problem in creating columns from CSV file

saugatasantra84 — Mon, 30 Oct 2017 16:36:31 GMT

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

Re: Facing problem in creating columns from CSV file

Tom — Mon, 30 Oct 2017 17:22:10 GMT

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

Re: Facing problem in creating columns from CSV file

Reeza — Mon, 30 Oct 2017 17:32:59 GMT

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

Re: Facing problem in creating columns from CSV file

saugatasantra84 — Mon, 30 Oct 2017 17:46:57 GMT

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

Re: Facing problem in creating columns from CSV file

Tom — Mon, 30 Oct 2017 17:54:34 GMT

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

Re: Facing problem in creating columns from CSV file

Reeza — Mon, 30 Oct 2017 18:06:11 GMT

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

No you cannot increase. Yes you can rename it, but you're still limited to 32 characters.