[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: Re: PROC Export to ASCII: What am I doing wrong.
From: "peter.crawford" <peter.crawford () BLUEYONDER ! CO ! UK>
Date: 2014-01-28 20:48:59
Message-ID: dpu2nxave2spijcclysacn75.1390940882382 () email ! android ! com
[Download RAW message or body]
Just avoid having proc export touch Oracle directly. If you need no subsetting of \
oracle table rows, a datastep view is simplest. Into the beginning of your macro \
insert this data step and change the DATA= in the proc export to refer to that view. \
Data viewo/view=viewo ;
Set data.&file ;
Run;
Proc export data= viewo
outfile=
........ etc
If you need to apply a where as you run proc export (if it now supports a where on \
the DATA= ) then your performance might be better with a proc sql view. However it \
doesn't look like you have WHERE in mind.
Good luck
Peter
<div>-------- Original message --------</div><div>From: Yusuf Mohamedshah \
<zulsdad@gmail.com> </div><div>Date:28/01/2014 18:13 (GMT+00:00) </div><div>To: \
Peter Crawford <Peter.Crawford@blueyonder.co.uk> </div><div>Subject: Re: PROC Export \
to ASCII: What am I doing wrong. </div><div> </div>Thanks for your reply. What is the \
alternative? I need to convert 13 files for a two year period so total 26 files. And \
all the thirteen files have unique names hence I was trying to use the macro to speed \
up the process. I did try to use the Export Wizard one file at a time and it works. \
But I wanted to avoid running the wizard 26 times.
Thanks
On Tue, Jan 28, 2014 at 12:57 PM, Peter Crawford <Peter.Crawford@blueyonder.co.uk> \
wrote: Jusuf
If you have provided lowcase table name to proc export and it chose to make
it upper case, then
***********************************************************
you might have to abandon proc export.
***********************************************************
There are effective alternatives
The simplest takes almost fewer lines.
But I might be wrong.
You haven't confirmed (yet) this defect in proc export (just my humble
opinion).
peter
From: Yusuf Mohamedshah [mailto:zulsdad@gmail.com]
Sent: 28 January 2014 17:39
To: Peter Crawford
Subject: Re: PROC Export to ASCII: What am I doing wrong.
Thanks Peter for your reply. The log does indicated that the table name is
uppercase when passwed to Oracle. And in Oracle the table name is lower
case. Probably hence the error. So how do I make sure that when SAS passes
the table name to Oracle it is lower case and not uppercase. This is my
modified macro:
%macro ascii(file, yr, type);
PROC EXPORT DATA= data.&file.
OUTFILE= "D:\HSIS\IRB Review\data\wa&yr.&type..txt"
DBMS=DLM REPLACE;
DELIMITER='00'x;
RUN;
%mend ascii;
%ascii(wa10acc, 10, acc)
In above macro I want to make sure that &file. is lower case when passed on
to Oracle.
Thanks for all your help.
On Tue, Jan 28, 2014 at 12:18 PM, Peter Crawford
<Peter.Crawford@blueyonder.co.uk> wrote:
> I see the original data library is in Oracle.
>
> Probably like most dbms, SAS allows those table names to be "case
sensitive.
> In that way the Oracle db could have tables named WA10acc and wa10ACC but
> certainly doesn't have one named WA10ACC, because SAS has reported that
that
> table doesn't exist.
>
> Yusuf, you probably need to use the exact table name as it appears in
Oracle
> - in mixed case. I think the SAS explorer windows might not present the
name
> as it is expected by Oracle.
> good luck
> peterC
>
> ,
>
> On Tue, 28 Jan 2014 11:07:22 -0600, Joe Matise <snoopy369@GMAIL.COM> wrote:
>
> > Two things I'd check:
> > * is '00'x a legal delimiter? Try changing it to '09'x and see if that
> > works.
> > * 'data' is not exactly a reserved word (nothing in SAS really is), but
> > it's something I avoid as a libname as I worry SAS will confuse it with
> > other things. Perhaps try changing that.
> >
> > Then, try running the proc export by hand (not with the wizard, but not in
> > a macro either). See if you're somehow causing an issue by being in a
> > macro. I don't see anything obvious there but who knows. Also verify
that
> > it's not a timing issue relative to when you created the connection to
> > oracle (perhaps it hadn't finished figuring things out when you ran the
> > first code) or even an issue with schema/etc. that you fixed while running
> > the wizard. You also could try running the wizard's code by hand (copy
> > from log directly) and see if that works.
> >
> > I would in general guess the error message might be a fallacious one -
> > sometimes in SAS you get error messages for unrelated things when
something
> > else goes wrong that's not properly error-handled, that may be what's
> > happening here.
> >
> > -Joe
> >
> >
> > On Tue, Jan 28, 2014 at 10:29 AM, Yusuf Mohamedshah
> <zulsdad@gmail.com>wrote:
> >
> > > Dear SAS-L'ers
> > >
> > > I am exporting a SAS data file to ASCII using following macro:
> > >
> > > %macro ascii(yr, type);
> > > PROC EXPORT DATA= data.wa&yr.&type.
> > > OUTFILE= "D:\HSIS\IRB Review\data\wa&yr.&type..txt"
> > > DBMS=DLM REPLACE;
> > > DELIMITER='00'x;
> > > RUN;
> > > %mend ascii;
> > >
> > > %ascii(10, acc)
> > >
> > > But the log says Export was unsuccesful since data.wa10acc does not
> exist.
> > >
> > > However when I run Export Wizard the file gets exported successfully.
> > > Also the Export Wizard creates the SAS code to do the export and when I
> run
> > > that SAS code I get same error saying that file DATA.WA10ACC.DATA does
> not
> > > exist. I am puzzled.
> > >
> > > Following is the output from LOG:
> > >
> > > NOTE: AUTOEXEC processing beginning; file is
> > > d:\hsis\new_server\autoexec.sas.
> > > NOTE: Libref DATA was successfully assigned as follows:
> > > Engine: ORACLE
> > > Physical Name: prodw
> > > NOTE: AUTOEXEC processing completed.
> > > 1 PROC EXPORT DATA= DATA.wa10acc
> > > 2 OUTFILE= "D:\HSIS\IRB Review\data\wa10acc.txt"
> > > 3 DBMS=TAB REPLACE;
> > > 4 PUTNAMES=YES;
> > > 5 RUN;
> > > ERROR: File DATA.WA10ACC.DATA does not exist.
> > > ERROR: Export unsuccessful. See SAS Log for details.
> > > NOTE: The SAS System stopped processing this step because of errors.
> > > NOTE: PROCEDURE EXPORT used (Total process time):
> > > real time 0.12 seconds
> > > cpu time 0.04 seconds
> > >
> > > This is when I run Export Wizard:
> > >
> > > 6
> > > /**********************************************************************
> > > 7 * PRODUCT: SAS
> > > 8 * VERSION: 9.2
> > > 9 * CREATOR: External File Interface
> > > 10 * DATE: 28JAN14
> > > 11 * DESC: Generated SAS Datastep Code
> > > 12 * TEMPLATE SOURCE: (None Specified.)
> > > 13
> > > ***********************************************************************/
> > > 14 data _null_;
> > > 15 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
> > > 16 %let _EFIREC_ = 0; /* clear export record count macro
> variable
> > > */
> > > 17 file 'D:\HSIS\IRB Review\data\wa10acc.txt' delimiter='09'x DSD
> > > DROPOVER lrecl=32767;
> > > 18 if _n_ = 1 then /* write column names or labels */
> > > 19 do;
> > > 20 put
> > > 21 "rd_inv"
> > > 22 '09'x
> > > 23 "milepost"
> > > 24 '09'x
> > > 25 "rodwycls"
> > > 26 '09'x
> > > 27 "caseno"
> > > 28 '09'x
> > > 29 "accyr"
> > > 30 '09'x
> > > 31 "rte_nbr"
> > > 32 '09'x
> > > 33 "rd_type"
> > > 34 '09'x
> > > 35 "rdqual"
> > > 36 '09'x
> > > 37 "maintdt"
> > > 38 '09'x
> > > 39 "ac_srmpi"
> > > 40 '09'x
> > > 41 "district"
> > > 42 '09'x
> > > 43 "county"
> > > 44 '09'x
> > > 45 "city"
> > > 46 '09'x
> > > 47 "rur_urb"
> > > 48 '09'x
> > > 49 "func_cls"
> > > 50 '09'x
> > > 51 "month"
> > > 52 '09'x
> > > 53 "daymth"
> > > 54 '09'x
> > > 55 "weekday"
> > > 56 '09'x
> > > 57 "time"
> > > 58 '09'x
> > > 59 "admclass"
> > > 60 '09'x
> > > 61 "prefx_cd"
> > > 62 '09'x
> > > 63 "sr_adid"
> > > 64 '09'x
> > > 65 "rd_qual"
> > > 66 '09'x
> > > 67 "v1cmpdir"
> > > 68 '09'x
> > > 69 "v1dircde"
> > > 70 '09'x
> > > 71 "v1event1"
> > > 72 '09'x
> > > 73 "acctype"
> > > 74 '09'x
> > > 75 "v2cmpdir"
> > > 76 '09'x
> > > 77 "v2dircde"
> > > 78 '09'x
> > > 79 "v2event1"
> > > 80 '09'x
> > > 81 "impact"
> > > 82 '09'x
> > > 83 "report"
> > > 84 '09'x
> > > 85 "severity"
> > > 86 '09'x
> > > 87 "tot_inj"
> > > 88 '09'x
> > > 89 "tot_kill"
> > > 90 '09'x
> > > 91 "tot_ped"
> > > 92 '09'x
> > > 93 "numvehs"
> > > 94 '09'x
> > > 95 "loc_type"
> > > 96 '09'x
> > > 97 "rd_char1"
> > > 98 '09'x
> > > 99 "rdsurf"
> > > 100 '09'x
> > > 101 "rd_rel"
> > > 102 '09'x
> > > 103 "light"
> > > 104 '09'x
> > > 105 "coltype1"
> > > 106 '09'x
> > > 107 "coltype2"
> > > 108 '09'x
> > > 109 "object1"
> > > 110 '09'x
> > > 111 "object2"
> > > 112 '09'x
> > > 113 "agency"
> > > 114 '09'x
> > > 115 "timenote"
> > > 116 '09'x
> > > 117 "hazmat"
> > > 118 '09'x
> > > 119 "fire"
> > > 120 '09'x
> > > 121 "timearr"
> > > 122 '09'x
> > > 123 "gps_latx"
> > > 124 '09'x
> > > 125 "gps_laty"
> > > 126 '09'x
> > > 127 "gps_latz"
> > > 128 '09'x
> > > 129 "wkzone"
> > > 130 '09'x
> > > 131 "hit_run"
> > > 132 '09'x
> > > 133 "xrdclass"
> > > 134 '09'x
> > > 135 "no_peds"
> > > 136 '09'x
> > > 137 "streport"
> > > 138 '09'x
> > > 139 "cls_traff"
> > > 140 '09'x
> > > 141 "intent"
> > > 142 '09'x
> > > 143 "nonmvind"
> > > 144 '09'x
> > > 145 "medcause"
> > > 146 '09'x
> > > 147 "rev_mp"
> > > 148 '09'x
> > > 149 "ac_srmp"
> > > 150 '09'x
> > > 151 "rec_type"
> > > 152 '09'x
> > > 153 "st_func"
> > > 154 '09'x
> > > 155 "loc_char"
> > > 156 '09'x
> > > 157 "weather"
> > > 158 ;
> > > 159 end;
> > > 160 set DATA.wa10acc end=EFIEOD;
> > > 161 format rd_inv $11. ;
> > > 162 format milepost best12. ;
> > > 163 format rodwycls $2. ;
> > > 164 format caseno $10. ;
> > > 165 format accyr $4. ;
> > > 166 format rte_nbr $3. ;
> > > 167 format rd_type $2. ;
> > > 168 format rdqual $6. ;
> > > 169 format maintdt $8. ;
> > > 170 format ac_srmpi $1. ;
> > > 171 format district $1. ;
> > > 172 format county $2. ;
> > > 173 format city $4. ;
> > > 174 format rur_urb $1. ;
> > > 175 format func_cls $2. ;
> > > 176 format month $2. ;
> > > 177 format daymth $2. ;
> > > 178 format weekday $1. ;
> > > 179 format time $4. ;
> > > 180 format admclass $1. ;
> > > 181 format prefx_cd $1. ;
> > > 182 format sr_adid $3. ;
> > > 183 format rd_qual $6. ;
> > > 184 format v1cmpdir $1. ;
> > > 185 format v1dircde $1. ;
> > > 186 format v1event1 $1. ;
> > > 187 format acctype $2. ;
> > > 188 format v2cmpdir $1. ;
> > > 189 format v2dircde $1. ;
> > > 190 format v2event1 $1. ;
> > > 191 format impact $2. ;
> > > 192 format report $1. ;
> > > 193 format severity $1. ;
> > > 194 format tot_inj best12. ;
> > > 195 format tot_kill best12. ;
> > > 196 format tot_ped best12. ;
> > > 197 format numvehs best12. ;
> > > 198 format loc_type $1. ;
> > > 199 format rd_char1 $1. ;
> > > 200 format rdsurf $1. ;
> > > 201 format rd_rel $1. ;
> > > 202 format light $1. ;
> > > 203 format coltype1 $2. ;
> > > 204 format coltype2 $2. ;
> > > 205 format object1 $2. ;
> > > 206 format object2 $2. ;
> > > 207 format agency $1. ;
> > > 208 format timenote $4. ;
> > > 209 format hazmat $1. ;
> > > 210 format fire $1. ;
> > > 211 format timearr $4. ;
> > > 212 format gps_latx best12. ;
> > > 213 format gps_laty best12. ;
> > > 214 format gps_latz best12. ;
> > > 215 format wkzone $1. ;
> > > 216 format hit_run $1. ;
> > > 217 format xrdclass $1. ;
> > > 218 format no_peds best12. ;
> > > 219 format streport $1. ;
> > > 220 format cls_traff $1. ;
> > > 221 format intent $1. ;
> > > 222 format nonmvind $1. ;
> > > 223 format medcause $1. ;
> > > 224 format rev_mp best12. ;
> > > 225 format ac_srmp best12. ;
> > > 226 format rec_type best12. ;
> > > 227 format st_func $2. ;
> > > 228 format loc_char $2. ;
> > > 229 format weather $2. ;
> > > 230 do;
> > > 231 EFIOUT + 1;
> > > 232 put rd_inv $ @;
> > > 233 put milepost @;
> > > 234 put rodwycls $ @;
> > > 235 put caseno $ @;
> > > 236 put accyr $ @;
> > > 237 put rte_nbr $ @;
> > > 238 put rd_type $ @;
> > > 239 put rdqual $ @;
> > > 240 put maintdt $ @;
> > > 241 put ac_srmpi $ @;
> > > 242 put district $ @;
> > > 243 put county $ @;
> > > 244 put city $ @;
> > > 245 put rur_urb $ @;
> > > 246 put func_cls $ @;
> > > 247 put month $ @;
> > > 248 put daymth $ @;
> > > 249 put weekday $ @;
> > > 250 put time $ @;
> > > 251 put admclass $ @;
> > > 252 put prefx_cd $ @;
> > > 253 put sr_adid $ @;
> > > 254 put rd_qual $ @;
> > > 255 put v1cmpdir $ @;
> > > 256 put v1dircde $ @;
> > > 257 put v1event1 $ @;
> > > 258 put acctype $ @;
> > > 259 put v2cmpdir $ @;
> > > 260 put v2dircde $ @;
> > > 261 put v2event1 $ @;
> > > 262 put impact $ @;
> > > 263 put report $ @;
> > > 264 put severity $ @;
> > > 265 put tot_inj @;
> > > 266 put tot_kill @;
> > > 267 put tot_ped @;
> > > 268 put numvehs @;
> > > 269 put loc_type $ @;
> > > 270 put rd_char1 $ @;
> > > 271 put rdsurf $ @;
> > > 272 put rd_rel $ @;
> > > 273 put light $ @;
> > > 274 put coltype1 $ @;
> > > 275 put coltype2 $ @;
> > > 276 put object1 $ @;
> > > 277 put object2 $ @;
> > > 278 put agency $ @;
> > > 279 put timenote $ @;
> > > 280 put hazmat $ @;
> > > 281 put fire $ @;
> > > 282 put timearr $ @;
> > > 283 put gps_latx @;
> > > 284 put gps_laty @;
> > > 285 put gps_latz @;
> > > 286 put wkzone $ @;
> > > 287 put hit_run $ @;
> > > 288 put xrdclass $ @;
> > > 289 put no_peds @;
> > > 290 put streport $ @;
> > > 291 put cls_traff $ @;
> > > 292 put intent $ @;
> > > 293 put nonmvind $ @;
> > > 294 put medcause $ @;
> > > 295 put rev_mp @;
> > > 296 put ac_srmp @;
> > > 297 put rec_type @;
> > > 298 put st_func $ @;
> > > 299 put loc_char $ @;
> > > 300 put weather $ ;
> > > 301 ;
> > > 302 end;
> > > 303 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR
> > > detection macro variable */
> > > 304 if EFIEOD then call symputx('_EFIREC_',EFIOUT);
> > > 305 run;
> > > NOTE: The file 'D:\HSIS\IRB Review\data\wa10acc.txt' is:
> > > Filename=D:\HSIS\IRB Review\data\wa10acc.txt,
> > > RECFM=V,LRECL=32767,File Size (bytes)=0,
> > > Last Modified=28Jan2014:11:20:30,
> > > Create Time=28Jan2014:11:20:17
> > > NOTE: 42461 records were written to the file 'D:\HSIS\IRB
> > > Review\data\wa10acc.txt'.
> > > The minimum record length was 170.
> > > The maximum record length was 553.
> > > NOTE: There were 42460 observations read from the data set DATA.wa10acc.
> > > NOTE: DATA statement used (Total process time):
> > > real time 3.52 seconds
> > > cpu time 0.84 seconds
> > >
> > > 42460 records created in D:\HSIS\IRB Review\data\wa10acc.txt from
> > > DATA.wa10acc.
> > >
> > > NOTE: "D:\HSIS\IRB Review\data\wa10acc.txt" file was successfully
> created.
> > > Thanks for all your help.
> > >
> > > --
> > > Yusuf Mohamedshah
> > >
--
Yusuf Mohamedshah
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic