BookmarkSubscribeRSS Feed
saugatasantra84
Calcite | Level 5

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

20 REPLIES 20
Reeza
Super User

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
Calcite | Level 5

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;

 

andreas_lds
Jade | Level 19

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
its not working
Reeza
Super User

@saugatasantra84 wrote:
its not working

What does that mean?

saugatasantra84
Calcite | Level 5

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
Super User Tom
Super User

@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

Reeza
Super User

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

saugatasantra84
Calcite | Level 5
We cannot increase it? or if we want to rename?
Tom
Super User Tom
Super User

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

 

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