topic Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row? in SAS Procedures

variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

Ken_oy — Tue, 01 Feb 2011 17:11:56 GMT

variable names in row 2,
data starts from row 3

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

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

deleted_user — Mon, 19 Dec 2016 14:29:04 GMT

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

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

ballardw — Wed, 02 Feb 2011 20:58:29 GMT

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.

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

Ksharp — Wed, 16 Feb 2011 05:05:12 GMT

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

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

rsva — Fri, 29 Jul 2011 12:23:57 GMT

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

Thanks.

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

art297 — Fri, 29 Jul 2011 12:50:00 GMT

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

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

rsva — Fri, 29 Jul 2011 13:00:44 GMT

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.

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

hellind — Sat, 16 Jun 2012 08:15:23 GMT

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?

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

Ksharp — Mon, 18 Jun 2012 01:58:14 GMT

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

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

hellind — Mon, 18 Jun 2012 10:44:40 GMT

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.

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

Ksharp — Tue, 19 Jun 2012 02:20:05 GMT

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

Ksharp

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

R_A_G_ — Wed, 02 Jan 2013 21:52:39 GMT

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

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

art297 — Wed, 02 Jan 2013 22:50:15 GMT

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.

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

phani9909 — Wed, 16 Sep 2015 11:21:33 GMT

try getnames=no

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

sundeep15685 — Sun, 27 Dec 2015 15:44:31 GMT

Check this code if your file is in xlsx format.

In the range statement, you can change it if your data starts from 3rd row or any row.

PROC IMPORT DATAFILE="c:\users\asr\desktop\sas practice\tasktwo.xlsx"
OUT=sports_sale
DBMS=XLSX
REPLACE;
range="input$A2:C2000";
Getnames=yes;
informat Time_stamp datetime16.;
run;

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

daren — Thu, 20 Oct 2016 15:58:52 GMT

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;

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

SwingCorey — Wed, 08 Nov 2017 17:07:41 GMT

I found that the NAMEROW option only works for Excel files, not CSVs (or other delimited text files).

The way I solved a similar problem (the first 9 lines of each text file are file headers, not column headings - those are on line 10 with data starting at line 11) was to use a temporary file where the output of the original starts at the line with the column labels.

For example, my code looks like this:

  /* Need to cut the first 9 rows that do not contain header info */
   options obs=50; /* just use 50 lines for testing purposes - set to max to production */
   filename tempdata temp;

     data _null_;
       infile "&filenm." 
           firstobs=10
           lrecl=32760
           dlm=','
           missover
           dsd ;
       file tempdata dlm=',' lrecl=32760;
       input;
       put _infile_;
     run;

   /* import from temp file */
   proc import out=work.test datafile=tempdata
      dbms=csv replace;
      datarow=2;
      getnames=yes;
   run;

Of course, &filenm is a macro variable holding the full path filename of the file you are attempting to import.

"firstobs=10" allows me to skip those first 9 useless lines.

"filename tempdata temp" sets up a text file in the WORK folder that is output to in the "file tempdata dlm=',' lrecl=32760;" step of the "data _NULL_" block and then becomes the input of the PROC IMPORT block at the end.

The PROC IMPORT works as usual (DBMS=CSV for comma-delimited, DATAROW=2 to instruct IMPORT that the data starts on the 2nd line of the new/temporary file; GETNAMES=YES uses the column labels from the former 10th line - now top line - of the file).

I hope that covers everything.

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

mich1 — Wed, 21 Feb 2018 21:42:29 GMT

You could also try to import the data and rename all the fields using the code below...

/*IMPORT THE CSV FILE*/
PROC IMPORT OUT= WORK.GET
DATAFILE= <YOUR FILE LOCATION HERE>
DBMS=CSV REPLACE;
GETNAMES=Yes;
GUESSINGROWS=300;
RUN;
/*TRANSPOSE TO RENAME VARIABLES*/
proc transpose data=GET(obs=1) out=temp;
var _all_;
run;
data temp (DROP = <SOME COLUMNS HERE>);
set temp;
format NEWNAME $20.;
NEWNAME = compress(COL1);
<YOURNAMING CONVENTION HERE>
run;
/*LOAD THE NEWNAMES INTO A MACRO VARIABLE*/
proc sql ;
select catx('=',_name_,NEWNAME)
into :rename separated by ' '
from temp;
quit;
/*RENAME VARIABLES WITH NEW NAME*/
data GET;
set GET(firstobs=2 rename=(&rename));
run;

Re: variable names in "row 2" how to let PROC IMPORT to start from the 2rd row?

VioSas — Thu, 08 Nov 2018 15:31:13 GMT

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