BookmarkSubscribeRSS Feed
wfiona020
Calcite | Level 5

if I have an excel file looks like this. 

Screenshot 2022-11-09 at 11.07.59 AM.png

 

I use proc import detafile=.......... out=projoct_data;

and have a table like this:

Capture.PNG

 

How could I fix it? 

Moreover, I want to row 2 in excel to become the name of data in the sas table, not F6,7,8,9...

Thanks

3 REPLIES 3
Tom
Super User Tom
Super User

Show the actual CODE you used to read the excel file.

Do you actually have an XLSX file?  Or did you accidentally let Excel open a CSV file?

 

If you have an XLSX file then use the RANGE statement to tell it start in cell A2 instead of A1.

 range='$A2:';

Here is example:

%let fname = %sysfunc(pathname(work))/class.xlsx;

proc export data=sashelp.class file="&fname" dbms=xlsx replace;
run;

proc import file="&fname" dbms=xlsx out=test1 replace;
run;

proc import file="&fname" dbms=xlsx out=test2 replace;
  range='$A2:';
  getnames=NO;
run;

proc compare data=test1 compare=test2;
  var name sex age height weight;
  with a b c d e;
run;

 

If you have a CSV file then skip the PROC IMPORT and write your own data step to read it.  Use the FIRSTOBS=3 option on the INFILE statement to skip both header lines.

wfiona020
Calcite | Level 5
It’s xlsx , not csv.
How can I change name too?
Reeza
Super User
To get the names, use GETNAMES=YES or leave that option out and just specify the range starts at A2. If you know the ending of the range of data and this is a one time import it's sometimes useful to specify the end of the range, ie A2:J28

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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
  • 3 replies
  • 296 views
  • 0 likes
  • 3 in conversation