Category: SAS data editing

PROC IMPORT OUT= WORK.list
DATAFILE= ".....xlsx"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

PROC EXPORT DATA= all3
OUTFILE= "C:\ ... \name_of_file.xlsx"
DBMS=EXCEL REPLACE;
SHEET="data check";
RUN;

PROC IMPORT OUT= WORK.asdf
DATAFILE= ".xlsx"
DBMS=EXCEL REPLACE;
RANGE="'Administrative Data$'";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

PROC EXPORT DATA= sashelp.Verbmgr OUTFILE= "C:\temp\example.xls"
DBMS=EXCEL2000 REPLACE;
RUN;

PROC IMPORT OUT= exceldata
DATAFILE= "C:\temp\example.xls"
DBMS=EXCEL2000 REPLACE;
GETNAMES=YES;
RUN;

Examples of format statements

FORMAT disaster_date date9. ;

format ProjectNumber $12.;

data sample;
input id $ var1 var2 var3;
datalines;
A 20 . .
A . 30 40
B 20 . .
B 20 . .
C . 10 .
C 10 . .
C . . 10
;

data sample;
set sample;
by id;
array v(3) var1-var3;
array n(3) v1-v3;
retain v1-v3;
do i = 1 to 3;
if v(i) ne . then n(i)=v(i);
end;
if last.id then do;
output;
do j = 1 to 3;
n(j)=.;
end;
end;
drop i j var1-var3;
rename v1=var1 v2=var2 v3=var3;

proc print;
run;

Thanks GP.

This replaces all letters into xxxx.

data a;
q=" Licensed to ICF CONSULTING GROUP, Site 70007867. ";
prx=prxparse("s/[A-Z]/x/io");
call prxchange(prx,-1,q);
drop prx;
run;

proc print;run;

This replaces all letters and numeric values into xxx.

data a;
q=" Licensed to ICF CONSULTING GROUP, Site 70007867. ";
prx=prxparse("s/[A-Z,0-9]/x/io");
call prxchange(prx,-1,q);
drop prx;
run;

proc print;run;

Thanks, KR.