BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sundeep15685
Obsidian | Level 7

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;

mich1
Obsidian | Level 7

Robot wink

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;

Bread Crumbs and Circuses for All
SwingCorey
Calcite | Level 5

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.

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
  • 214185 views
  • 11 likes
  • 14 in conversation