Hi,
I have .xlsb raw data and once it is imported in SAS, the data structure will be as shown below. My requirement is to rename the column names automatically from the First row values as showin in Desired data. Please help me in achieving this.
PROC IMPORT OUT= test
DATAFILE= "C:\Book1.xlsb"
DBMS=EXCEL REPLACE;
RANGE="sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
--test dataset data structure (After Importing structure will be as shown below)
ABC | F1 | F2 | FDG |
ID | Name | Sex | Country |
1 | ABC | M | IND |
2 | BCD | F | USA |
3 | CDE | M | GER |
4 | DGE | M | UK |
--Desired Data (from the test dataset how to achieve as shown below)
ID | Name | Sex | Country |
1 | ABC | M | IND |
2 | BCD | F | USA |
3 | CDE | M | GER |
4 | DGE | M | UK |
Thanks in Advance.
Hi 1239,
try this one (there are other solutions at https://communities.sas.com/message/122471#122471😞
data have;
input (ABC F1 F2 FDG)($);
cards;
ID Name Sex Country
1 ABC M IND
2 BCD F USA
3 CDE M GER
4 DGE M UK
;
proc transpose data=have(obs=1) out=temp;
var _all_;
run;
proc sql ;
select catx('=',_name_,col1)
into :rename separated by ' '
from temp;
quit;
data have;
set have(firstobs=2 rename=(&rename));
run;
proc print;run;
Obs ID Name Sex Country
1 1 ABC M IND
2 2 BCD F USA
3 3 CDE M GER
4 4 DGE M UK
Linlin
Thanks for the code but I got the below error.
Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
Please help me.
I got the below error:
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
179 namerow=2;
-------
180
180 startrow=3;
--------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
181 getnames=yes;
182 run;
NOTE: The SAS System stopped processing this step because of errors.
Tested and working fine.
PROC IMPORT OUT=WORK.OP
FILE="C:\Documents and Settings\test.xlsb"
DBMS=EXCEL REPLACE;
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=NO;
SCANTIME=YES;
DBSASLABEL=NONE;
TEXTSIZE=512;
RANGE="Invoice$A2:D6";
RUN;
proc print; run;
Output:
Obs ID Name Sex Country
1 1 ABC M IND
2 2 BCD F USA
3 3 CDE M GER
4 4 DGE M UK
Log:
112 PROC IMPORT OUT=WORK.OP
113 FILE="C:\Documents and Settings\test.xlsb"
114 DBMS=EXCEL REPLACE;
115 GETNAMES=YES;
116 SCANTEXT=YES;
117 USEDATE=NO;
118 SCANTIME=YES;
119 DBSASLABEL=NONE;
120 TEXTSIZE=512;
121 RANGE="Invoice$A2:D6";
122
123 RUN;
NOTE: WORK.OP data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 34.92 seconds
cpu time 1.12 seconds
125 proc print; run;
NOTE: There were 4 observations read from the data set WORK.OP.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
I got below error.
ERROR: File _IMEX_.'Invoice$A2:D6'n.DATA does not exist.
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1.35 seconds
cpu time 0.04 seconds
I think small confusion has created. My .xlsb data structure will be different.
If you look at my orginal post after importing .xlsb, I will create one dataset called "test". Test dataset structure will be as shown below. From test dataset variable names to be renamed.
PROC IMPORT OUT= test
DATAFILE= "C:\Book1.xlsb"
DBMS=EXCEL REPLACE;
RANGE="sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
--test dataset data structure (After Importing structure will be as shown below)
ABC | F1 | F2 | FDG |
ID | Name | Sex | Country |
1 | ABC | M | IND |
2 | BCD | F | USA |
3 | CDE | M | GER |
4 | DGE | M | UK |
--Desired Data (from the test dataset how to achieve as shown below)
ID | Name | Sex | Country |
1 | ABC | M | IND |
2 | BCD | F | USA |
3 | CDE | M | GER |
4 | DGE | M | UK |
This can be done in 2 ways. One is the way that I proposed. Dropping the first row when importing. The second one is the process you proposed.
Talking about the first process, the error occured because the file you are trying to import for example "A2:D6". That resolves the error.
If you want to do it the other way, see the below code.
data have;
input ABC$ F1$ F2 $ FDG $;
cards;
ABC F1 F2 FDG
ID Name Sex Country
1 ABC M IND
2 BCD F USA
3 CDE M GER
4 DGE M UK
run;
data want (RENAME=(ABC = ID
F1=Name
F2=sex
FDG=Country));
set have;
if _n_=1 then delete;
run;
proc print; run;
Sometimes column names will be more than what I specified in real time. Is there any way to capture the first rows values and renaming the column names automatically instead of explicity specifying in the code?
ABC F1 F2 FDG F3 GH KL OI.....
Thanks
Then I would go with the first process where we have the ability to drop first row while importing. Change the bold values according to your need.
PROC IMPORT OUT=WORK.OP ** Change the library name and DSN**;
FILE="C:\Documents and Settings\test.xlsb" ** Change the Path**;
DBMS=EXCEL REPLACE;
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=NO;
SCANTIME=YES;
DBSASLABEL=NONE;
TEXTSIZE=512;
RANGE="Invoice$A2:D6"; ** Replace the word Invoice with your Sheet name**; ** Enter the range of data that you want. Make sure you exclude the first row **;
RUN;
proc print; run;
Try it and let me know how it works.
I will check if there are other options available related to this scenario.
There is one more way. Please check the below link.
Hi 1239,
try this one (there are other solutions at https://communities.sas.com/message/122471#122471😞
data have;
input (ABC F1 F2 FDG)($);
cards;
ID Name Sex Country
1 ABC M IND
2 BCD F USA
3 CDE M GER
4 DGE M UK
;
proc transpose data=have(obs=1) out=temp;
var _all_;
run;
proc sql ;
select catx('=',_name_,col1)
into :rename separated by ' '
from temp;
quit;
data have;
set have(firstobs=2 rename=(&rename));
run;
proc print;run;
Obs ID Name Sex Country
1 1 ABC M IND
2 2 BCD F USA
3 3 CDE M GER
4 4 DGE M UK
Linlin
Thanks a lot, the code is working as expected.
Maybe you can try to define a range like :
PROC IMPORT OUT= test
DATAFILE= "C:\Book1.xlsb"
DBMS=EXCEL REPLACE;
RANGE='sheet1$A1:X100'n;
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Ksharp
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.