I am trying to import data from an excel file into SAS University. I have 5 species and a dozen or so variables.
When i run Proc IMPORT and Proc PRINT of the data it will read some of the Species and their rows of data, but not all. In fact, it will read 3 rows of data but not the other 275.
Why isn't SAS reading data that is clearly visible in the excel file itself? or How can i force SAS to read the data that it won't?
this is the code i've written:
%web_drop_table(WORK.IMPORT);
FILENAME FILEREF '/folders/myfolders/Trees.xlsx';
PROC IMPORT DATAFILE=FILEREF
DBMS=XLSX
OUT=WORK.IMPORT;
GETNAMES=YES;
RUN;
data Trees;
set import;
if Measurement=2;
if GLD>0;
if DBH>0;
if Height>0;
run;
proc print data=Trees;
run;
Thanks in advance
FILEREF may be a keyword, try changing your FILENAME reference to have a different word.
FILENAME myfile '/folders/myfolders/Trees.xlsx';
PROC IMPORT DATAFILE=myfile
DBMS=XLSX
OUT=WORK.IMPORT;
GETNAMES=YES;
RUN;
And post your log.
@AaronJ wrote:
I am trying to import data from an excel file into SAS University. I have 5 species and a dozen or so variables.
When i run Proc IMPORT and Proc PRINT of the data it will read some of the Species and their rows of data, but not all. In fact, it will read 3 rows of data but not the other 275.
Why isn't SAS reading data that is clearly visible in the excel file itself? or How can i force SAS to read the data that it won't?
this is the code i've written:
%web_drop_table(WORK.IMPORT);
FILENAME FILEREF '/folders/myfolders/Trees.xlsx';
PROC IMPORT DATAFILE=FILEREF
DBMS=XLSX
OUT=WORK.IMPORT;
GETNAMES=YES;
RUN;data Trees;
set import;
if Measurement=2;
if GLD>0;
if DBH>0;
if Height>0;
run;
proc print data=Trees;
run;
Thanks in advance
Which data set only has 3 rows of data? Work.import or work.trees?
In
data Trees;
set import;
if Measurement=2;
if GLD>0;
if DBH>0;
if Height>0;
Each of those If statements removed records. Your proc print should only show the records where Measurement=2 and GLD>0 and DBH>0 and height > 0 because you removed everything else in the set import when writing it to Trees.
Post your log.
Here's the log:
And how many observations and variables were you expecting?
NOTE: The import data set has 12488 observations and 21 variables.
of the 12k observations in total, with the constraints i put on the data with If statements i was expecting about 250 observations to be read by SAS.
I have five species, and i used an If statement to constrain the Sort to only one species in the example. When i shift that If statement to other species, they also return incorrect numbers of observations.
So your problem is with the IF statements not your import.
I would verify that the data is first being read in correctly and check how you're filtering.
Remember when filtering on character variables they are case sensitive.
Also, your log shows code that's different from what you posted, so what exactly is the code you're having issues with?
And can you post sample data? If not, run a PROC FREQ or MEANS on your data sets to check that it's correct and you're not overlooking something.
@AaronJ wrote:
yes, i am aware of that. and there are still 270 lines of data with numbers in them for each of the variables that SAS is not reading.
Are you basing the above statement from looking at the data in the original Excel files?
You may be thinking you have species values=1 or measurement values =2 that are actually 1.01 or .99 (for species) or 2.01 or 1.99 because the display in Excel was set to no decimals.
You might want to print some of those records you think should be included from the IMPORT set to show the values of measurement, gld, dbh and height.
It also helps to be consistent: Original post:
if Measurement=2; if GLD>0; if DBH>0; if Height>0;
in the longer code example:
74 if Species=1; 75 if Measurement=2; 76 if GLD>0.01; 77 if DBH>0.01; 78 if Height>0.01;
which is more restrictive than with the addition of Species and increasing the limits of the last three variables.
BTW I notice you have code like this:
Thanks for all the input everyone. I found my mistake: i didn't realize that the chain of IF statements was an implicit AND. Many of my data didn't have BDH values so the program i wrote was excluding them. When i removed the IF statement for DBH, SAS found all the data that i was expecting.
Thanks again.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.