DATA Step, Macro, Functions and more

How to add missing columns in existing dataset

Reply
Contributor
Posts: 70

How to add missing columns in existing dataset

[ Edited ]

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)

 

Super User
Posts: 23,342

Re: How to add missing columns in existing dataset

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)

 


 

Contributor
Posts: 70

Re: How to add missing columns in existing dataset

Reeza,

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

 

Occasional Contributor
Posts: 15

Re: How to add missing columns in existing dataset

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

Ask a Question
Discussion stats
  • 3 replies
  • 112 views
  • 1 like
  • 3 in conversation