*
; /* Newsgroups: comp.soft-sys.sas Date: Fri, 4 Sep 1998 09:27:00 +1000 Reply-To: Hockey Richard;Sender: "SAS(r) Discussion" From: Hockey Richard Subject: SAS2DBF and DBF2SAS : Repost Hi Some people had a bit of trouble with the attachments so here they are again in plain text. RH */ %macro dbf2sas(in=,out=,del=N,keep=,drop=,where=, dfmt=,); %put %str( ) ; %put %str( ) Macro to convert dbase file to SAS dataset Ver 3.0b ; %put %str( ) Author Richard Hockey DPH Sep 1998 ; %put %str( ) Email: rhockey@mater.org.au; %put %str( ) ; %if %quote(&in)= %then %do; %put %str( ) Usage: ; %put %str( ) %nrstr(%DBF2SAS(IN= ,OUT= ,DEL= ,KEEP= ,DROP= ,WHERE= , DFMT=);) ; %put %str( ) Parameters are: ; %put %str( ) IN dbase file name (eg something.dbf) without quotes.; %put %str( ) OUT output SAS dataset. ; %put %str( ) Optional parameters: ; %put %str( ) DEL Keep deleted records Y/N (Default=N). ; %put %str( ) either KEEP Variables to keep. ; %put %str( ) or DROP Variables to drop. ; %put %str( ) WHERE subsetting where clause. ; %put %str( ) DFMT date format in dataset (Default=DDMMYY6.). %put %str( ) (as a concession to those US users). ; %goto endmacro; %end; /* determine byte order */ data _null_; if put(input('1234'x,ib2.),hex4.)='1234' then endian='BIG '; else if put(input('1234'x,ib2.),hex4.)='3412' then endian='LITTLE'; else put "Can't determine byte order of this computer!!"; call symput('endian',endian); run; %if &endian= %then %goto endmacro; data _temp ; infile "&in" recfm=n lrecl=256; length fmt $ 10; input vn pib1. year ib1. month ib1. day ib1. nr $4. hs $2. lr $2. +20 @; %if &endian=BIG %then %do; nr=reverse(nr); hs=reverse(hs); lr=reverse(lr); %end; nrec=input(nr,ib4.); heads=input(hs,ib2.); lenrec=input(lr,ib2.); nfields=int(heads/32)-1; call symput('_nr',left(put(nrec,12.))); call symput('_hs',left(put(heads,12.))); put; put "Date of dbf file= " day z2. "/" month z2. "/" year; put "Version= " vn; put "Number of Fields= " nfields ; put "Number of Records= " nrec; put "Length of Header= " heads ; put "Length of Records= " lenrec ; put; do i=1 to nfields; input name $11. type $1. +4 flen pib1. fdec ib1. +14 @; if first^=1 then do; file "control.sas"; first=1; put "DATA &out;"; put " FORMAT " ; end; name=substr(name,1,verify(name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_') -1); if length(name)>8 then do; file log; vcount+1; name1="VDB"||put(vcount,z2.); put "Variable " name " too long, changed to " name1; name=name1; end; if type in('C','L') then do; if flen > 200 or fdec > 0 then do; file log; put "Variable " name " Field length too long, truncated to 1st 200 characters" /; ; fmt="$200."; end; else fmt="$"||trim(left(put(flen,8.)))||"."; end; else if type in('N','F') then fmt=trim(left(put(flen,8.)))||"."||trim(left(put(fdec,8.))); else if type='D' then %if &dfmt^= %then %do; fmt="&dfmt" ; %end; %else %do; fmt="DDMMYY6."; %end; file "control.sas"; if type^='M' then put name fmt ; output; end; put ";"; stop; run; data _null_; set _temp end=eof; length range $ 25 ; file "control.sas" mod; if _n_ =1 then do; put "INFILE '&in' recfm=n lrecl=256 ;"; put "START= &_hs ;"; put "INPUT +START @;" ; put "DO J=1 TO &_NR ;" ; put "INPUT del $1. " ; end; if type='D' then range=" "||name||" YYMMDD"||trim(left(put(flen,8.)))||". " ; else if type in("C","L") then do; if flen > 200 or fdec > 0 then do; if fdec=0 then range=name||" $200."||" +"||trim(left(put((flen-200),8.))) ; else range=name||" $200."||" +"||trim(left(put(((flen+(fdec*256))-200),8.))); end; else range=name||" $"||trim(left(put(flen,8.)))||". "; end; else if type in('N','F') then range=name||" "||trim(left(put(flen,8.)))||"."||trim(left(put(fdec,8.))); else if type='M' then range="+10" ; put range ; if eof then do; put "@ ;" ; %if &del=N %then %do; put "IF DEL^='*'"; %if %nrbquote(&where)^= %then put " AND (&where)";; put " THEN OUTPUT;"; put "ELSE IF DEL='*' THEN DO;"; put " FILE LOG;"; put " PUT 'RECORD ' J ' DELETED';"; put "END;"; %end; %else %do; %if nrbquote(&where)^= %then put "IF (&where) THEN";; put "OUTPUT;"; %end; put "END;"; put "DROP "; %if &del=N %then %do; put " DEL"; %end; put " START J;"; put "STOP;"; %if %nrbquote(&drop)^= %then put "DROP &drop;" %str(;); %else %if %nrbquote(&keep)^= %then put "KEEP &keep;";; put "RUN;"; end; run; %inc control; %endmacro: %mend dbf2sas; *