I have problem to import data from Excel to SAS dataset. When the Excel file has the observation is (as below) in row, I just use the usual import wizard, then done with the sas code.
ProjNum | Claim | Adv | Rare | LastClaim |
1 | 4 | 1 | 0 | 1 |
2 | 19 | 1 | 0.72 | 0 |
3 | 12 | 0 | 0.3721 | 1 |
4 | 9 | 0 | 0.054 | 0 |
PROC IMPORT OUT= WORK.SME
DATAFILE= "E:\project\file.xls"
DBMS=EXCEL REPLACE;
RANGE="SME$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
If I have the observation is (as below) in column, the import wizard will import it but in wrong variables, what should i do?
ProjNum | 1 | 2 | 3 | 4 |
ClaimNum | 4 | 19 | 12 | 9 |
PreAdv | 1 | 1 | 0 | 0 |
PreAdjR | 0 | 0.72 | 0.3721 | 0.054 |
LasClaim | 1 | 0 | 1 | 0 |
Thank you
You could always transpose it, but I'd be interested in seeing where the problem originates. Can you attach a copy of the spreadsheet to your post?
Hi,
Try this below code.I got the output without any errors,
PROC IMPORT OUT= SME
DATAFILE= "E:\project\file.xls"
DBMS=EXCEL REPLACE;
GETNAMES=YES;
MIXED=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Thanks
Okay, after re-reading your post, I presume that the data are simply exactly as shown in the spreadsheet. If so, I stick with my original suggestion, but it takes a couple of steps. Try something like:
PROC IMPORT OUT= WORK.SME
DATAFILE= "E:\project\file.xls"
DBMS=EXCEL REPLACE;
RANGE="SME$";
GETNAMES=NO;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
proc transpose data=sme out=want (drop=_:);
var f2-f5;
run;
proc sql noprint;
select 'col'||put(monotonic(),1.)||'='||F1
into : renames separated by " "
from sme
;
quit;
data want;
set want (rename=(&renames.));
run;
I think that will correct your problem.
Art
First, thanks for your (and others) replies.
One piece I don't undstand from your code. I am new to Proc sql. There, I see you are creating:
col1=ProjNum
col2=ClaimNum
col3=PreAdv
col4=PreAdjR
col5=LasClaim
then you store them in the Macro variable 'renames'. (right?)
What does the part of ' separated by " " ' do?
-----------------------------------------------------------------
proc sql noprint;
select 'col'||put(monotonic(),1.)||'='||F1
into : renames separated by " "
from sme
;
quit;
data want;
set want (rename=(&renames.));
/*not sure what is it doing here. Create in Sasdata set a new variable 'rename' taking values from Macro variable '&renames.' */
/*why there is a dot following '&renames' */
run;
-----------------------------------------------------------
I got the error message when I try your code.
330 proc sql ;
331 select 'col'||put(monotonic(),1.)||'='||F1
332 into : renames separated by " "
333 from npo1
334 ;
335 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
336 data want;
337 set want (rename=(&renames.));
ERROR: Variable col1 is not on file WORK.WANT.
ERROR: Variable col2 is not on file WORK.WANT.
ERROR: Variable col3 is not on file WORK.WANT.
ERROR: Variable col4 is not on file WORK.WANT.
ERROR: Variable col5 is not on file WORK.WANT.
ERROR: Variable col6 is not on file WORK.WANT.
ERROR: Variable col7 is not on file WORK.WANT.
ERROR: Variable col8 is not on file WORK.WANT.
ERROR: Variable col9 is not on file WORK.WANT.
ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.WANT.
338 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
As I indicated in a later post, Tom's suggested transpose code using the ID statement, accomplishes the same thing and much more directly.
Anyhow, to answer your question, the separated by " " simply puts spaces between all of the information. If you don't included the separated by space (i.e., " ") statement you would end up with:
col1=ProjNumcol2=ClaimNumcol3=PreAdvcol4=PreAdjRcol5=LasClaim
which SAS wouldn't know what to do with.
As for the error you got, it looks like you either didn't run the proc transpose as I had suggested, or included something else in the call that changed the variable names. Otherwise, proc transpose would have created new variables labeled by column numbers.
Again, Tom's suggested code obviates the need to run anything other than the procs import and transpose.
If its a one-off the transpose in excel via Paste Special>Transpose.
If its occurring with multiple datasets then use DDE to read in the file.
Just make sure to use the GETNAMES=NO setting on PROC IMPORT.
Then use PROC TRANSPOSE in SAS.
proc transpose data=have out=want;
id f1;
var f2 - f5 ;
run;
Tom's suggestion accomplishes the same thing that my suggested code does, but I definitely recommend using his!
And you can drop the name and label variables that gets created:
PROC IMPORT OUT= WORK.test
DATAFILE= "XXXXXXXXXXXXX.xls"
DBMS=EXCEL REPLACE;
SHEET="XXXXXXX";
GETNAMES=NO;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
proc transpose data=test out=desired(drop=_name_ _label_);
id f1;
run;
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 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.