BookmarkSubscribeRSS Feed
tekish
Quartz | Level 8

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)

 

3 REPLIES 3
Reeza
Super User

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)

 


 

tekish
Quartz | Level 8

Reeza,

I want add  the missing column with N/A values.

 

mahesh146
Obsidian | Level 7

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);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

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
  • 1787 views
  • 1 like
  • 3 in conversation