Dear Experts,
I have more than 1000 excel files and I want to import into SAS.
I want to add EXT1-EXT9 to all the excel file.
If ANY MISSING EXT1-EXT9 then "n/a" add to the file.
Can any one help how to add the macro to add the missing columns, I have attached tested data.
/****************************************/
/* MACRO TO READ EXCEL FILE */
/****************************************/
%MACRO XLS(FN);
proc import datafile="C:\temp\&FN..xlsx"
dbms=excelcs replace out=temp;
run;
data TEMP (keep = TERRITORY REGION AREA EXT1-EXT9 HOLDFN);
retain TERRITORY REGION AREA EXT1-EXT9 HOLDFN;
length REGION $6 HOLDFN $60 ;
set temp;
HOLDFN = "&FN";
LABEL HOLDFN = 'Returned Excel file name';
output;
end;
run;
PROC APPEND BASE = TRA2 DATA = TEMP FORCE;
RUN;
%mend XLS;
%xls(st1)
%xls(st2)
%xls(st3)
You want to add the variables which are character I assume with the values N/A or with missing values?
@tekish wrote:
Dear Experts,
I have more than 1000 excel files and I want to import into SAS.
I want to add EXT1-EXT9 to all the excel file.
If ANY MISSING EXT1-EXT9 then "n/a" add to the file.
Can any one help how to add the macro to add the missing columns, I have attached tested data.
/****************************************/
/* MACRO TO READ EXCEL FILE */
/****************************************/
%MACRO XLS(FN);
proc import datafile="C:\temp\&FN..xlsx"
dbms=excelcs replace out=temp;
run;
data TEMP (keep = TERRITORY REGION AREA EXT1-EXT9 HOLDFN);
retain TERRITORY REGION AREA EXT1-EXT9 HOLDFN;
length REGION $6 HOLDFN $60 ;
set temp;
HOLDFN = "&FN";
LABEL HOLDFN = 'Returned Excel file name';
output;
end;
run;
PROC APPEND BASE = TRA2 DATA = TEMP FORCE;
RUN;
%mend XLS;
%xls(st1)
%xls(st2)
%xls(st3)
Reeza,
I want add the missing column with N/A values.
Please check if below code helps.
%LET FolderPath= /folders/myfolders/sasuser.v94/test;
%MACRO Excel_Load(file);
PROC IMPORT DATAFILE="&folderpath/&file..xlsx"
DBMS=XLSX
REPLACE
OUT=TEMP
;
RUN;
DATA TEMP;
SET TEMP;
ARRAY EXT{9} $;
DO i=1 to 9;
IF EXT{i}=' ' THEN EXT{i}='n/a';
END;
DROP i;
RUN;
PROC PRINT DATA=TEMP;
TITLE "&file..xlsx";
RUN;
%LET tmp_final_id= %SYSFUNC(OPEN(TEMP_FINAL));
%LET rc=%SYSFUNC(CLOSE(&tmp_final_id));
%PUT 'tmp_final_id='&tmp_final_id 'rc='&rc;
%IF &tmp_final_id=0 %THEN %DO;
DATA TEMP_FINAL;
SET TEMP;
RUN;
%END;
%ELSE %DO;
DATA TEMP_FINAL;
SET TEMP_FINAL TEMP;
RUN;
%END;
%MEND Excel_Load;
PROC DATASETS NOLIST;
DELETE TEMP_FINAL;
RUN;
%Excel_Load(ST1);
%Excel_Load(ST2);
%Excel_Load(ST3);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.