topic Re: How to add missing columns in existing dataset in SAS Programming

How to add missing columns in existing dataset

tekish — Sun, 03 Jun 2018 20:49:30 GMT

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)

Re: How to add missing columns in existing dataset

Reeza — Sun, 03 Jun 2018 21:51:32 GMT

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)

Re: How to add missing columns in existing dataset

mahesh146 — Mon, 04 Jun 2018 08:27:50 GMT

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

Re: How to add missing columns in existing dataset

tekish — Wed, 06 Jun 2018 18:20:54 GMT

Reeza,

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