BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ken_oy
Fluorite | Level 6
variable names in row 2,
data starts from row 3

is there any option to control this? Thanks Message was edited by: Ken_oy
1 ACCEPTED SOLUTION

Accepted Solutions
deleted_user
Not applicable

Editor’s Note: This is a popular topic. Thanks to @deleted_user for providing the solution below for .XLS and delimited files. Thanks to @Ksharp for sample code solution for .XLS files and to @sundeep15685 for providing a .XLSX file sample code solution.  

Additional information for comma, tab or delimited files: There is not an option to tell SAS which record to pick up the variable names from when reading a comma, tab or delimited file. If your variable names are not on row 1, then you will need to use the GETNAMES=NO and DATAROW=n where n is the starting record of your data. Here is an example of skipping the first rows of data and reading data starting from row 4 while SAS assigns the variable names as VARn where n is a number:

 

proc import datafile='c:\temp\class.csv' out=class dbms=csv replace;

getnames=no;

datarow=4;

run;

 

Hello,

you didn' mention what kind of file you are trying to import (delimited, xls)...

if your file is of .xls type you can adjust the import procedure with RANGE and GETNAMES=YES options.

if your file is of .txt type:
- you can switch from import procedure to date step with infile (with option firstobs=3) and input statements (with variables named here)
- you can use import procedure with GETNAMES=NO, DATAROW=3, GUESSINGROWS= (default 20 - you may increase it depending on your file size) and after importing it use a data step or proc datasets to change the variables name.

HTH,
Marius

View solution in original post

18 REPLIES 18
deleted_user
Not applicable

Editor’s Note: This is a popular topic. Thanks to @deleted_user for providing the solution below for .XLS and delimited files. Thanks to @Ksharp for sample code solution for .XLS files and to @sundeep15685 for providing a .XLSX file sample code solution.  

Additional information for comma, tab or delimited files: There is not an option to tell SAS which record to pick up the variable names from when reading a comma, tab or delimited file. If your variable names are not on row 1, then you will need to use the GETNAMES=NO and DATAROW=n where n is the starting record of your data. Here is an example of skipping the first rows of data and reading data starting from row 4 while SAS assigns the variable names as VARn where n is a number:

 

proc import datafile='c:\temp\class.csv' out=class dbms=csv replace;

getnames=no;

datarow=4;

run;

 

Hello,

you didn' mention what kind of file you are trying to import (delimited, xls)...

if your file is of .xls type you can adjust the import procedure with RANGE and GETNAMES=YES options.

if your file is of .txt type:
- you can switch from import procedure to date step with infile (with option firstobs=3) and input statements (with variables named here)
- you can use import procedure with GETNAMES=NO, DATAROW=3, GUESSINGROWS= (default 20 - you may increase it depending on your file size) and after importing it use a data step or proc datasets to change the variables name.

HTH,
Marius

VioSas
Calcite | Level 5

We can use 'Range' to import specific row/columns from the input file.

 

proc import out = work.input_file datafile='c:\temp\class.xlsx'

dbms=xlsx replace;

range="'Data - Groups'$A7 : O12";

GETNAMES=YES;

run;

 

 

Thanks

ballardw
Super User
Assuming this is some sort of text file.

If I only had one file to read: Remove the first line then proceed with import.

If I had multiple files in the exact same format: Remove the first line from one file.
Use Proc Import, Copy the code from the LOG that was generated to read the file,
clean it up, modify the FIRSTOBS=2 to FIRSTOBS=3 and use appropriate FILENAME references to read each file.

I use the second frequently as my clients change data file layouts frequently. I then modify the informat, format and input statements as well as adding labels and any calculated variables.
Ksharp
Super User
Hi.
[pre]
proc import file='c:\temp\op.xls' out=op dbms=xls replace;
namerow=2;
startrow=3;
getnames=yes;
run;
[/pre]
But this way is not suited for office2007.


Ksharp
rsva
Fluorite | Level 6

How do you use namerow and startrow options with range option to import  excel files with xlsx extension

Thanks.

art297
Opal | Level 21

You don't unless the file is small enough that you can save it as an older version (i.e., xls) of Excel.

rsva
Fluorite | Level 6

Thanks. I was planning to do that. But I have to resave 50 files. I wanted to see if there is a easier way to do it SAS with existing file extension.

hellind
Quartz | Level 8

Date: 30/04/2012

Limits & Utilization Report

Risk Type: CRE_RT

Entity: DBS SIN

Segment Code: Emerging Business (25, 30)

SEGMENT CODE;RMNAME;COUNTERPARTY GROUP;COUNTERPARTY;COUNTERPARTYFULLNAME;LIMITGROUP;PILLAR;CURRENCY;LIMITS;UTILIZATION;AVAILABLE;EXPIRYDATE;LIMITCOMMENT;CIFNUMBER;MARKETVALUE;MARKETVALUEGROSS;ADDON;ADDONGROSS

15;BBCSC 83 EMBZ - NON;3DXXRESPT1;3DXXRESPT1;3D COMPANY PTE. LTD.;FX;O/N;USD;55000;0.00;55000.00;;;30202040;0.000000;0.000000;0.000000;0.000000

PROC IMPORT DATAFILE='\\Portfolio Data\FX\lim_util_cre_rt_15_20.csv'

  OUT=FX_THISMONTH

  DBMS=CSV  REPLACE;

  GETNAMES=NO;

  DELIMITER=';';

  DATAROW=7;

  GUESSINGROWS=10000;

RUN;

The header row is 6 and data row starts at 7. How do I set PROC IMPORT to get the headers from row-6?

Ksharp
Super User

I think your code looks good . Didn't it work ?

PROC IMPORT DATAFILE='\\Portfolio Data\FX\lim_util_cre_rt_15_20.csv'

  OUT=FX_THISMONTH

  DBMS=CSV  REPLACE;

  GETNAMES=NO;

  DELIMITER=';';

  DATAROW=6;

  GUESSINGROWS=10000;

RUN;

Ksharp

hellind
Quartz | Level 8

Row-6 should be the header, and Row-7 is the data.

I had to use multile proc import and proc export to get the data I want, with row-6 as header and row-7 as data. I was hoping to acheive that in one proc.

PROC IMPORT DATAFILE='\\Portfolio Data\FX\lim_util_cre_rt_15_20.csv'

  OUT=FX_THISMONTH

  DBMS=CSV  REPLACE;

  GETNAMES=NO;

  DELIMITER=';';

  DATAROW=6;

  GUESSINGROWS=10000;

RUN;

filename exTemp temp;

proc export data=FX_THISMONTH

  outfile=exTemp

  dbms=csv REPLACE;

run;

filename exTemp1 temp;

data _null_;

   infile exTemp firstobs=2;

   file exTemp1;

   input;

   put _infile_;

run;

PROC IMPORT DATAFILE =exTemp1

  OUT=FX_THISMONTH1

  DBMS=CSV REPLACE;

  GETNAMES=YES;

  DATAROW=2;

  GUESSINGROWS=10000;

RUN;

I am using SAS 9.1.3 and hence I can't use PUTNAMES in proc export.

Ksharp
Super User

Or you can firstly delete the first 6 row in CSV file, as you did .

Ksharp

daren
Calcite | Level 5

I read in from row 2 as a character dataset since names will be characters. Save as a csv file without putting names, then read in from csv file getting names. Sample code below for a tab delimited source file:

 

proc import out=test file="&path\tabfile" dbms=tab replace;
getnames=no; datarow=2; guessingrows=9999; run;

 

proc export data=test file="&path\tabfile.csv" dbms=csv replace;
putnames = no; run;

 

proc import out=mydata file="&path\tabfile.csv" dbms=csv replace;
getnames=yes; guessingrows=9999; run;

R_A_G_
Calcite | Level 5

Hi,

I have the same issue but using Ksharp's code doesn't work, Am I doing anything wrong?

PROC IMPORT FILE="C:\Users\galeshi\Desktop\ALI\CV.CSV" out=CV dbms=CSV replace;

namerow=2;

startrow=3;

getnames=yes;run;

thanks

art297
Opal | Level 21

Did you try his second set of code (i.e., the one just above your post)?,

Some of the options you are specifying are only for importing excel files.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 18 replies
  • 214173 views
  • 11 likes
  • 14 in conversation