Solved: Appling STNAME Function in PROC SQL and other formats

E_money12 · Posted 01-18-2020 06:00 PM

Hello, I am new to proc SQL and I want to figure out how to apply a stnamel() format to my variable "RP State Plate"N to my table citationstotal within Proc SQL.

Here are my code for informats and formats if you need it.

%macro citationsimport (exportname=, infiledataset=);
FILENAME &infiledataset ;
data &exportname;
%let _EFIERR_ = 0;
infile &infiledataset delimiter=',' MISSOVER DSD firstobs=2;
  informat "Ticket number"N $32.;
  informat "Issue Date"N anydtdtm40.;
  informat "Issue time"N best32.;
  informat "Meter Id"N $6.;
  informat "Marked Time"N best32.;
  informat "RP State Plate"N $2.;
  informat "Plate Expiry Date"N best32.;
  informat VIN $1.;
  informat Make $4.;
  informat "Body Style"N $2.;
  informat Color $2.;
  informat Location $16.;
  informat Route $32.;
  informat Agency best32.;
  informat "Violation code"N $20.;
  informat "Violation Description"N $22.;
  informat "Fine amount"N best32.;
  informat Latitude best32.;
  informat Longitude best32.;
  format "Ticket number"N $12.;
  format "Issue Date"N datetime.;
  format "Issue time"N best12.;
  format "Meter Id"N $6.;
  format "Marked Time"N best12.;
  format "RP State Plate"N $2.;
  format "Plate Expiry Date"N best12.;
  format VIN $1.;
  format Make $4.;
  format "Body Style"N $2.;
  format Color $2.;
  format Location $16.;
  format Route $12.;
  format Agency best12.;
  format "Violation code"N $20.;
  format "Violation Description"N $22.;
  format "Fine amount"N best12.;
  format Latitude best12.;
  format Longitude best12.;
  input "Ticket number"N "Issue Date"N "Issue time"N "Meter Id"N $
"Marked Time"N "RP State Plate"N $
"Plate Expiry Date"N VIN $
Make $
"Body Style"N $
Color $
Location $
Route Agency "Violation code"N $
"Violation Description"N $
"Fine amount"N Latitude Longitude;
if _ERROR_ then
call symputx('_EFIERR_', 1);
run;
%MEND;

The bold and underline code is where i want to format my variable

proc sql;
create table citationstemp AS SELECT *
FROM work.import1
OUTER UNION corresponding 
SELECT *
FROM work.import2 
OUTER UNION corresponding 
SELECT *
FROM work.import3 
OUTER UNION corresponding 
SELECT *
FROM work.import4 
OUTER UNION corresponding 
SELECT *
FROM work.import5;

create table citationstotal as select * from work.citationstemp where not missing("Fine amount"N) order by "Ticket number"N;
select "Violation Description"N, count(*) as frequency from work.citationstotal group by "Violation Description"N order by frequency desc;
select "Violation code"N, count(*) as frequency from work.citationstotal group by "Violation code"N order by frequency desc;
select "RP State Plate"N, count(*) as frequency from work.citationstotal group by "RP State Plate"N order by frequency desc;
select * from work.citationstotal (obs=100); 
quit;

Partial Output data.

Not necessary, but I also need help trying to combine the issue date and issue time. The issue time is a 24 hour HHMM . Along with changing the latitude and longitude to proper degree values.

Thank You.

Patrick · Posted 01-18-2020 08:25 PM

If you want to print SAS variable names "nicely" then use labels instead of SAS name literals. Not using valid SAS variable names makes coding just harder.

  informat RP_State_Plate $2.;
  label RP_State_Plate='RP State Plate';

stnamel() is not a format but a function. Here how this could look in a SQL Select

select 
  *,
  stnamel(RP_State_Plate) as state length=54, 
  issue_date*86400+input(put(Issue_time,$4.),hhmmss4.) as issue_dttm format=datetime20.

You could of course already create these variables in your data step.

In case you just want to append the data together then you could also use Proc Append.

With a SQL if it's just about appending data I'd use UNION CORR ALL

View solution in original post

Patrick · Posted 01-18-2020 08:25 PM

If you want to print SAS variable names "nicely" then use labels instead of SAS name literals. Not using valid SAS variable names makes coding just harder.

  informat RP_State_Plate $2.;
  label RP_State_Plate='RP State Plate';

stnamel() is not a format but a function. Here how this could look in a SQL Select

select 
  *,
  stnamel(RP_State_Plate) as state length=54, 
  issue_date*86400+input(put(Issue_time,$4.),hhmmss4.) as issue_dttm format=datetime20.

You could of course already create these variables in your data step.

In case you just want to append the data together then you could also use Proc Append.

With a SQL if it's just about appending data I'd use UNION CORR ALL

E_money12 · Posted 01-18-2020 08:43 PM

Thanks for the help,
The data was previously in 1 csv, but it was to large to import to my sas(1.2gb). So I split it up into 5 csvs so I could import it and later put it back together.
Would you also happen to know how I could format the longitude & latitude and plot it on a map.

Patrick · Posted 01-18-2020 09:02 PM

Would you also happen to know how I could format the longitude & latitude and plot it on a map.

I don't have experience with this and would have to look it up in the docu.

I suggest you ask this as this as a new question. Just post a working SAS data step which creates some representative sample data with what you have and then explain what you need and show as much as you can the desired result.

For reading the data:

SAS as such shouldn't have any issues to read a 1.2GB file. Not sure if there are any restrictions with the University edition though.

If splitting up your source data: You would make your life easier if you omit the first line with the column headings an give the chunks names which follow a naming pattern like SourceFile_1, SourceFile_2,.... as then you could still read them with a single data step using the column modifier.

data want(compress=yes);
   infile SourceFile_: ......

E_money12 · Posted 01-18-2020 11:42 PM

I can't figure out why it wont work now.

its just giving me asterisk for the issue_dttm

Kurt_Bremser · Posted 01-19-2020 03:38 AM

Please post logs using the {i} button, not as pictures.

Per your initial data step, issue_time is already numeric, so using the $4. format is wrong; use the numeric z4. format instead.

For tested code, post a sample of the csv file (again, use the {i} button), so we can run your import code against it.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

E_money12 · Posted 01-19-2020 03:52 AM

Ticket number,Issue Date,Issue time,Meter Id,Marked Time,RP State Plate,Plate Expiry Date,VIN,Make,Body Style,Color,Location,Route,Agency,Violation code,Violation Description,Fine amount,Latitude,Longitude
1103341116,2015-12-21T00:00:00,1251,,,CA,200304,,HOND,PA,GY,13147 WELBY WAY,01521,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1103700150,2015-12-21T00:00:00,1435,,,CA,201512,,GMC,VN,WH,525 S MAIN ST,1C51,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1104803000,2015-12-21T00:00:00,2055,,,CA,201503,,NISS,PA,BK,200 WORLD WAY,2R2,2,8939,WHITE CURB,58,6439997.9,1802686.4
1104820732,2015-12-26T00:00:00,1515,,,CA,,,ACUR,PA,WH,100 WORLD WAY,2F11,2,000,17104h,,6440041.1,1802686.2
1105461453,2015-09-15T00:00:00,115,,,CA,200316,,CHEV,PA,BK,GEORGIA ST/OLYMPIC,1FB70,1,8069A,NO STOPPING/STANDING,93,99999,99999
1106226590,2015-09-15T00:00:00,19,,,CA,201507,,CHEV,VN,GY,SAN PEDRO S/O BOYD,1A35W,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1106500452,2015-12-17T00:00:00,1710,,,CA,201605,,MAZD,PA,BL,SUNSET/ALVARADO,00217,1,8070,PARK IN GRID LOCK ZN,163,99999,99999
1106500463,2015-12-17T00:00:00,1710,,,CA,201602,,TOYO,PA,BK,SUNSET/ALVARADO,00217,1,8070,PARK IN GRID LOCK ZN,163,99999,99999
1106506402,2015-12-22T00:00:00,945,,,CA,201605,,CHEV,PA,BR,721 S WESTLAKE,2A75,1,8069AA,NO STOP/STAND AM,93,99999,99999
1106506413,2015-12-22T00:00:00,1100,,,CA,201701,,NISS,PA,SI,1159 HUNTLEY DR,2A75,1,8069AA,NO STOP/STAND AM,93,99999,99999
1106506424,2015-12-22T00:00:00,1100,,,CA,201511,,FORD,TR,WH,1159 HUNTLEY DR,2A75,1,8069AA,NO STOP/STAND AM,93,99999,99999
1106506435,2015-12-22T00:00:00,1105,,,CA,201701,,CHRY,PA,GO,1159 HUNTLEY DR,2A75,1,8069AA,NO STOP/STAND AM,93,99999,99999
1106506446,2015-12-22T00:00:00,1110,,,CA,201511,,BMW,PA,BK,1200 W MIRAMAR,2A75,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1106549754,2015-12-15T00:00:00,825,,,CA,201607,,PTRB,TR,BK,4TH/STATE,CM96,1,8069A,NO STOPPING/STANDING,93,99999,99999
1107179581,2015-12-27T00:00:00,1055,,,CA,201605,,TOYO,PA,BK,3100 N HOLLYRIDGE DR,,54,8058L,PREF PARKING,68,99999,99999
1107179592,2015-12-27T00:00:00,1200,,,CA,201602,,MBNZ,PA,BK,3115 N BERENDO DR,,54,8058L,PREF PARKING,68,99999,99999
1107179603,2015-12-27T00:00:00,1400,,,CA,201611,,NISS,PA,WH,3100 N BEACHWOOD DR,,54,8058L,PREF PARKING,68,99999,99999
1107539823,2015-09-16T00:00:00,2120,,,CA,201502,,NISS,PA,,BLAINE/11TH PL,1FB95,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1107539834,2015-09-16T00:00:00,1045,,,CA,,,CHEV,PA,BK,1246 S FIGUEROA ST,1L20,1,8069AP,NO STOP/STAND PM,93,99999,99999
1107780811,2015-12-22T00:00:00,1102,,,CA,201606,,HOND,PA,BK,PLATA/RAMPART,2A1,1,8069B,NO PARKING,73,99999,99999
1107780822,2015-12-22T00:00:00,1105,,,FL,201611,,FORD,PA,WH,,2A1,1,8069B,NO PARKING,73,99999,99999
1107973985,2015-12-18T00:00:00,1920,,,CA,201509,,NISS,PA,BK,VICAR/CASTLE HEIGHTS,8A95,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1107973996,2015-12-18T00:00:00,1930,,,CA,201511,,HOND,PA,BK,REGENT ST/IVY ST,8A95,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1107978852,2015-12-18T00:00:00,1900,,,CA,201511,,HYUN,PA,SI,1707 GLENDON AV,8A29,1,4000A1,NO EVIDENCE OF REG,50,99999,99999
1108311002,2015-12-22T00:00:00,140,,,CA,201603,,FORD,PA,RE,12113 BURBANK BL,01532,1,5200A,DSPLYPLATE A,25,99999,99999
1108311013,2015-12-22T00:00:00,150,,,CA,201610,,FORD,SU,GY,6936 AGNES AV,01514,1,5200A,DSPLYPLATE A,25,99999,99999
1108311024,2015-12-22T00:00:00,205,,,CA,201607,,MASE,PA,BL,12036 SATICOY ST,01503,1,5200A,DSPLYPLATE A,25,99999,99999
1108311035,2015-12-22T00:00:00,200,,,CA,201511,,BMW,PA,BK,7466 LAUREL CYN BL,01503,1,099,5204,25,99999,99999
1108311046,2015-12-22T00:00:00,225,,,CA,201512,,PONT,PA,SI,BURBANK BL/COLFAX AV,01535,1,5200A,DSPLYPLATE A,25,99999,99999
1108311050,2015-12-22T00:00:00,510,,,CA,201601,,TESL,PA,GY,11411 DONA PEGITA DR,,1,5200A,DSPLYPLATE A,25,99999,99999
1108319494,2015-12-22T00:00:00,135,,,CA,201601,,MBNZ,PA,WH,12355 BURBANK BL,01503,1,5200A,DSPLYPLATE A,25,99999,99999

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable _SASWSTEMP_ resolves to /home/u42020802/.sasstudio/.images/9368a2df-c8bd-441b-9b56-3372030d134f
 SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
 SYMBOLGEN:  Macro variable GRAPHINIT resolves to GOPTIONS RESET=ALL GSFNAME=_GSFNAME;
 NOTE: ODS statements in the SAS Studio environment may disable some output features.
 71         
 72         /*
 73         Parking Citation
 74         /*==========================================================================================================*/
 75         /*                          Custom Macros*/
 76         /*Compressed original imports to a macro for simple use    */
 77         /*==========================================================================================================*/
 78         %web_drop_table(WORK.citationstotal);
 MLOGIC(WEB_DROP_TABLE):  Beginning execution.
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is NUM)
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is STEPNEEDED)
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is STEPSTARTED)
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is DSNAME)
 SYMBOLGEN:  Macro variable SYSPBUFF resolves to (WORK.citationstotal)
 SYMBOLGEN:  Macro variable NUM resolves to 1
 SYMBOLGEN:  Macro variable DSNAME resolves to WORK.citationstotal
 MLOGIC(WEB_DROP_TABLE):  %DO %WHILE(&dsname ne) loop beginning; condition is TRUE.  
 SYMBOLGEN:  Macro variable DSNAME resolves to WORK.citationstotal
 MLOGIC(WEB_DROP_TABLE):  %IF condition %sysfunc(exist(&dsname)) is FALSE
 SYMBOLGEN:  Macro variable DSNAME resolves to WORK.citationstotal
 MLOGIC(WEB_DROP_TABLE):  %IF condition %sysfunc(exist(&dsname,view)) is FALSE
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is NUM)
 SYMBOLGEN:  Macro variable NUM resolves to 1
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is DSNAME)
 SYMBOLGEN:  Macro variable SYSPBUFF resolves to (WORK.citationstotal)
 SYMBOLGEN:  Macro variable NUM resolves to 2
 SYMBOLGEN:  Macro variable DSNAME resolves to 
 MLOGIC(WEB_DROP_TABLE):  %DO %WHILE() condition is FALSE; loop will not iterate again.
 SYMBOLGEN:  Macro variable STEPSTARTED resolves to 0
 MLOGIC(WEB_DROP_TABLE):  %IF condition &stepstarted is FALSE
 MLOGIC(WEB_DROP_TABLE):  Ending execution.
 79         %web_drop_table(WORK.citationsmonthyear);
 MLOGIC(WEB_DROP_TABLE):  Beginning execution.
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is NUM)
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is STEPNEEDED)
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is STEPSTARTED)
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is DSNAME)
 SYMBOLGEN:  Macro variable SYSPBUFF resolves to (WORK.citationsmonthyear)
 SYMBOLGEN:  Macro variable NUM resolves to 1
 SYMBOLGEN:  Macro variable DSNAME resolves to WORK.citationsmonthyear
 MLOGIC(WEB_DROP_TABLE):  %DO %WHILE(&dsname ne) loop beginning; condition is TRUE.  
 SYMBOLGEN:  Macro variable DSNAME resolves to WORK.citationsmonthyear
 MLOGIC(WEB_DROP_TABLE):  %IF condition %sysfunc(exist(&dsname)) is FALSE
 SYMBOLGEN:  Macro variable DSNAME resolves to WORK.citationsmonthyear
 MLOGIC(WEB_DROP_TABLE):  %IF condition %sysfunc(exist(&dsname,view)) is FALSE
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is NUM)
 SYMBOLGEN:  Macro variable NUM resolves to 1
 MLOGIC(WEB_DROP_TABLE):  %LET (variable name is DSNAME)
 SYMBOLGEN:  Macro variable SYSPBUFF resolves to (WORK.citationsmonthyear)
 SYMBOLGEN:  Macro variable NUM resolves to 2
 SYMBOLGEN:  Macro variable DSNAME resolves to 
 MLOGIC(WEB_DROP_TABLE):  %DO %WHILE() condition is FALSE; loop will not iterate again.
 SYMBOLGEN:  Macro variable STEPSTARTED resolves to 0
 MLOGIC(WEB_DROP_TABLE):  %IF condition &stepstarted is FALSE
 MLOGIC(WEB_DROP_TABLE):  Ending execution.
 80         %macro citationsimport (exportname=, infiledataset=);
 81         FILENAME &infiledataset ;
 82         data &exportname;
 83         %let _EFIERR_ = 0;
 84         infile &infiledataset delimiter=',' MISSOVER DSD firstobs=2;
 85         informat "Ticket number"N $32.;
 86         informat Issue_Date anydtdtm40.;
 87         informat Issue_Time $32.;
 88         informat "Meter Id"N $6.;
 89         informat "Marked Time"N best32.;
 90         informat RP_State_Plate $2.;
 91         informat "Plate Expiry Date"N best32.;
 92         informat VIN $1.;
 93         informat Make $4.;
 94         informat "Body Style"N $2.;
 95         informat Color $2.;
 96         informat Location $16.;
 97         informat Route $32.;
 98         informat Agency best32.;
 99         informat "Violation code"N $20.;
 100        informat "Violation Description"N $22.;
 101        informat Fine_amount best32.;
 102        informat Latitude best32.;
 103        informat Longitude best32.;
 104        format "Ticket number"N $12.;
 105        format Issue_Date datetime.;
 106        format Issue_Time $12.;
 107        format "Meter Id"N $6.;
 108        format "Marked Time"N best12.;
 109        format RP_State_Plate $2.;
 110        format "Plate Expiry Date"N best12.;
 111        format VIN $1.;
 112        format Make $4.;
 113        format "Body Style"N $2.;
 114        format Color $2.;
 115        format Location $16.;
 116        format Route $12.;
 117        format Agency best12.;
 118        format "Violation code"N $20.;
 119        format "Violation Description"N $22.;
 120        format Fine_amount best12.;
 121        format Latitude best12.;
 122        format Longitude best12.;
 123        input "Ticket number"N Issue_Date Issue_Time "Meter Id"N  $
 124            "Marked Time"N RP_State_Plate  $
 125            "Plate Expiry Date"N VIN  $
 126            Make  $
 127            "Body Style"N  $
 128            Color  $
 129            Location  $
 130            Route Agency "Violation code"N  $
 131            "Violation Description"N  $
 132            Fine_amount Latitude Longitude;
 133        if _ERROR_ then
 134        call symputx('_EFIERR_', 1);
 135        run;
 136        %MEND;
 137        options mprint symbolgen mlogic;
 138        %put %citationsimport (exportname=import1,
 138      ! infiledataset='/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv');
 MLOGIC(CITATIONSIMPORT):  Beginning execution.
 MLOGIC(CITATIONSIMPORT):  Parameter EXPORTNAME has value import1
 MLOGIC(CITATIONSIMPORT):  Parameter INFILEDATASET has value 
       '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv'
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv'
 FILENAME '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv'
 SYMBOLGEN:  Macro variable EXPORTNAME resolves to import1
 MPRINT(CITATIONSIMPORT):   data import1;
 MLOGIC(CITATIONSIMPORT):  %LET (variable name is _EFIERR_)
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv'
 MPRINT(CITATIONSIMPORT):   infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv' delimiter=',' MISSOVER 
 DSD firstobs=2;
 MPRINT(CITATIONSIMPORT):   informat "Ticket number"N $32.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Date anydtdtm40.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Time $32.;
 MPRINT(CITATIONSIMPORT):   informat "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   informat "Marked Time"N best32.;
 MPRINT(CITATIONSIMPORT):   informat RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   informat "Plate Expiry Date"N best32.;
 MPRINT(CITATIONSIMPORT):   informat VIN $1.;
 MPRINT(CITATIONSIMPORT):   informat Make $4.;
 MPRINT(CITATIONSIMPORT):   informat "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   informat Color $2.;
 MPRINT(CITATIONSIMPORT):   informat Location $16.;
 MPRINT(CITATIONSIMPORT):   informat Route $32.;
 MPRINT(CITATIONSIMPORT):   informat Agency best32.;
 MPRINT(CITATIONSIMPORT):   informat "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   informat "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   informat Fine_amount best32.;
 MPRINT(CITATIONSIMPORT):   informat Latitude best32.;
 MPRINT(CITATIONSIMPORT):   informat Longitude best32.;
 MPRINT(CITATIONSIMPORT):   format "Ticket number"N $12.;
 MPRINT(CITATIONSIMPORT):   format Issue_Date datetime.;
 MPRINT(CITATIONSIMPORT):   format Issue_Time $12.;
 MPRINT(CITATIONSIMPORT):   format "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   format "Marked Time"N best12.;
 MPRINT(CITATIONSIMPORT):   format RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   format "Plate Expiry Date"N best12.;
 MPRINT(CITATIONSIMPORT):   format VIN $1.;
 MPRINT(CITATIONSIMPORT):   format Make $4.;
 MPRINT(CITATIONSIMPORT):   format "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   format Color $2.;
 MPRINT(CITATIONSIMPORT):   format Location $16.;
 MPRINT(CITATIONSIMPORT):   format Route $12.;
 MPRINT(CITATIONSIMPORT):   format Agency best12.;
 MPRINT(CITATIONSIMPORT):   format "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   format "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   format Fine_amount best12.;
 MPRINT(CITATIONSIMPORT):   format Latitude best12.;
 MPRINT(CITATIONSIMPORT):   format Longitude best12.;
 MPRINT(CITATIONSIMPORT):   input "Ticket number"N Issue_Date Issue_Time "Meter Id"N $ "Marked Time"N RP_State_Plate $ "Plate Expiry 
 Date"N VIN $ Make $ "Body Style"N $ Color $ Location $ Route Agency "Violation code"N $ "Violation Description"N $ Fine_amount 
 Latitude Longitude;
 MPRINT(CITATIONSIMPORT):   if _ERROR_ then call symputx('_EFIERR_', 1);
 MPRINT(CITATIONSIMPORT):   run;
 
 NOTE: The infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv' is:
       Filename=/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv,
       Owner Name=u42020802,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=14Jan2020:07:17:45,
       File Size (bytes)=265554821
 
 NOTE: 1999999 records were read from the infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations1.csv'.
       The minimum record length was 58.
       The maximum record length was 164.
 NOTE: The data set WORK.IMPORT1 has 1999999 observations and 19 variables.
 NOTE: DATA statement used (Total process time):
       real time           4.43 seconds
       user cpu time       4.07 seconds
       system cpu time     0.36 seconds
       memory              2091.62k
       OS Memory           30632.00k
       Timestamp           01/19/2020 08:51:05 AM
       Step Count                        41  Switch Count  2
       Page Faults                       0
       Page Reclaims                     230
       Page Swaps                        0
       Voluntary Context Switches        16
       Involuntary Context Switches      4
       Block Input Operations            0
       Block Output Operations           908040
       
 
 MLOGIC(CITATIONSIMPORT):  Ending execution.
 139        %put %citationsimport (exportname=import2,
 139      ! infiledataset='/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv');
 MLOGIC(CITATIONSIMPORT):  Beginning execution.
 MLOGIC(CITATIONSIMPORT):  Parameter EXPORTNAME has value import2
 MLOGIC(CITATIONSIMPORT):  Parameter INFILEDATASET has value 
       '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv'
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv'
 FILENAME '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv'
 SYMBOLGEN:  Macro variable EXPORTNAME resolves to import2
 MPRINT(CITATIONSIMPORT):   data import2;
 MLOGIC(CITATIONSIMPORT):  %LET (variable name is _EFIERR_)
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv'
 MPRINT(CITATIONSIMPORT):   infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv' delimiter=',' MISSOVER 
 DSD firstobs=2;
 MPRINT(CITATIONSIMPORT):   informat "Ticket number"N $32.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Date anydtdtm40.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Time $32.;
 MPRINT(CITATIONSIMPORT):   informat "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   informat "Marked Time"N best32.;
 MPRINT(CITATIONSIMPORT):   informat RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   informat "Plate Expiry Date"N best32.;
 MPRINT(CITATIONSIMPORT):   informat VIN $1.;
 MPRINT(CITATIONSIMPORT):   informat Make $4.;
 MPRINT(CITATIONSIMPORT):   informat "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   informat Color $2.;
 MPRINT(CITATIONSIMPORT):   informat Location $16.;
 MPRINT(CITATIONSIMPORT):   informat Route $32.;
 MPRINT(CITATIONSIMPORT):   informat Agency best32.;
 MPRINT(CITATIONSIMPORT):   informat "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   informat "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   informat Fine_amount best32.;
 MPRINT(CITATIONSIMPORT):   informat Latitude best32.;
 MPRINT(CITATIONSIMPORT):   informat Longitude best32.;
 MPRINT(CITATIONSIMPORT):   format "Ticket number"N $12.;
 MPRINT(CITATIONSIMPORT):   format Issue_Date datetime.;
 MPRINT(CITATIONSIMPORT):   format Issue_Time $12.;
 MPRINT(CITATIONSIMPORT):   format "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   format "Marked Time"N best12.;
 MPRINT(CITATIONSIMPORT):   format RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   format "Plate Expiry Date"N best12.;
 MPRINT(CITATIONSIMPORT):   format VIN $1.;
 MPRINT(CITATIONSIMPORT):   format Make $4.;
 MPRINT(CITATIONSIMPORT):   format "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   format Color $2.;
 MPRINT(CITATIONSIMPORT):   format Location $16.;
 MPRINT(CITATIONSIMPORT):   format Route $12.;
 MPRINT(CITATIONSIMPORT):   format Agency best12.;
 MPRINT(CITATIONSIMPORT):   format "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   format "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   format Fine_amount best12.;
 MPRINT(CITATIONSIMPORT):   format Latitude best12.;
 MPRINT(CITATIONSIMPORT):   format Longitude best12.;
 MPRINT(CITATIONSIMPORT):   input "Ticket number"N Issue_Date Issue_Time "Meter Id"N $ "Marked Time"N RP_State_Plate $ "Plate Expiry 
 Date"N VIN $ Make $ "Body Style"N $ Color $ Location $ Route Agency "Violation code"N $ "Violation Description"N $ Fine_amount 
 Latitude Longitude;
 MPRINT(CITATIONSIMPORT):   if _ERROR_ then call symputx('_EFIERR_', 1);
 MPRINT(CITATIONSIMPORT):   run;
 
 NOTE: The infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv' is:
       Filename=/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv,
       Owner Name=u42020802,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=14Jan2020:07:17:58,
       File Size (bytes)=266029385
 
 NOTE: 2000000 records were read from the infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations2.csv'.
       The minimum record length was 58.
       The maximum record length was 179.
 NOTE: The data set WORK.IMPORT2 has 2000000 observations and 19 variables.
 NOTE: DATA statement used (Total process time):
       real time           4.45 seconds
       user cpu time       4.06 seconds
       system cpu time     0.40 seconds
       memory              2088.09k
       OS Memory           30632.00k
       Timestamp           01/19/2020 08:51:10 AM
       Step Count                        42  Switch Count  2
       Page Faults                       0
       Page Reclaims                     134
       Page Swaps                        0
       Voluntary Context Switches        23
       Involuntary Context Switches      5
       Block Input Operations            0
       Block Output Operations           908040
       
 
 MLOGIC(CITATIONSIMPORT):  Ending execution.
 140        %put %citationsimport (exportname=import3,
 140      ! infiledataset='/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv');
 MLOGIC(CITATIONSIMPORT):  Beginning execution.
 MLOGIC(CITATIONSIMPORT):  Parameter EXPORTNAME has value import3
 MLOGIC(CITATIONSIMPORT):  Parameter INFILEDATASET has value 
       '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv'
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv'
 FILENAME '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv'
 SYMBOLGEN:  Macro variable EXPORTNAME resolves to import3
 MPRINT(CITATIONSIMPORT):   data import3;
 MLOGIC(CITATIONSIMPORT):  %LET (variable name is _EFIERR_)
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv'
 MPRINT(CITATIONSIMPORT):   infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv' delimiter=',' MISSOVER 
 DSD firstobs=2;
 MPRINT(CITATIONSIMPORT):   informat "Ticket number"N $32.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Date anydtdtm40.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Time $32.;
 MPRINT(CITATIONSIMPORT):   informat "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   informat "Marked Time"N best32.;
 MPRINT(CITATIONSIMPORT):   informat RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   informat "Plate Expiry Date"N best32.;
 MPRINT(CITATIONSIMPORT):   informat VIN $1.;
 MPRINT(CITATIONSIMPORT):   informat Make $4.;
 MPRINT(CITATIONSIMPORT):   informat "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   informat Color $2.;
 MPRINT(CITATIONSIMPORT):   informat Location $16.;
 MPRINT(CITATIONSIMPORT):   informat Route $32.;
 MPRINT(CITATIONSIMPORT):   informat Agency best32.;
 MPRINT(CITATIONSIMPORT):   informat "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   informat "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   informat Fine_amount best32.;
 MPRINT(CITATIONSIMPORT):   informat Latitude best32.;
 MPRINT(CITATIONSIMPORT):   informat Longitude best32.;
 MPRINT(CITATIONSIMPORT):   format "Ticket number"N $12.;
 MPRINT(CITATIONSIMPORT):   format Issue_Date datetime.;
 MPRINT(CITATIONSIMPORT):   format Issue_Time $12.;
 MPRINT(CITATIONSIMPORT):   format "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   format "Marked Time"N best12.;
 MPRINT(CITATIONSIMPORT):   format RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   format "Plate Expiry Date"N best12.;
 MPRINT(CITATIONSIMPORT):   format VIN $1.;
 MPRINT(CITATIONSIMPORT):   format Make $4.;
 MPRINT(CITATIONSIMPORT):   format "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   format Color $2.;
 MPRINT(CITATIONSIMPORT):   format Location $16.;
 MPRINT(CITATIONSIMPORT):   format Route $12.;
 MPRINT(CITATIONSIMPORT):   format Agency best12.;
 MPRINT(CITATIONSIMPORT):   format "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   format "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   format Fine_amount best12.;
 MPRINT(CITATIONSIMPORT):   format Latitude best12.;
 MPRINT(CITATIONSIMPORT):   format Longitude best12.;
 MPRINT(CITATIONSIMPORT):   input "Ticket number"N Issue_Date Issue_Time "Meter Id"N $ "Marked Time"N RP_State_Plate $ "Plate Expiry 
 Date"N VIN $ Make $ "Body Style"N $ Color $ Location $ Route Agency "Violation code"N $ "Violation Description"N $ Fine_amount 
 Latitude Longitude;
 MPRINT(CITATIONSIMPORT):   if _ERROR_ then call symputx('_EFIERR_', 1);
 MPRINT(CITATIONSIMPORT):   run;
 
 NOTE: The infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv' is:
       Filename=/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv,
       Owner Name=u42020802,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=14Jan2020:07:18:34,
       File Size (bytes)=289184419
 
 NOTE: 2000000 records were read from the infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations3.csv'.
       The minimum record length was 66.
       The maximum record length was 180.
 NOTE: The data set WORK.IMPORT3 has 2000000 observations and 19 variables.
 NOTE: DATA statement used (Total process time):
       real time           4.52 seconds
       user cpu time       4.16 seconds
       system cpu time     0.36 seconds
       memory              2095.71k
       OS Memory           30632.00k
       Timestamp           01/19/2020 08:51:14 AM
       Step Count                        43  Switch Count  2
       Page Faults                       0
       Page Reclaims                     130
       Page Swaps                        0
       Voluntary Context Switches        19
       Involuntary Context Switches      4
       Block Input Operations            0
       Block Output Operations           908040
       
 
 MLOGIC(CITATIONSIMPORT):  Ending execution.
 141        %put %citationsimport (exportname=import4,
 141      ! infiledataset='/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv');
 MLOGIC(CITATIONSIMPORT):  Beginning execution.
 MLOGIC(CITATIONSIMPORT):  Parameter EXPORTNAME has value import4
 MLOGIC(CITATIONSIMPORT):  Parameter INFILEDATASET has value 
       '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv'
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv'
 FILENAME '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv'
 SYMBOLGEN:  Macro variable EXPORTNAME resolves to import4
 MPRINT(CITATIONSIMPORT):   data import4;
 MLOGIC(CITATIONSIMPORT):  %LET (variable name is _EFIERR_)
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv'
 MPRINT(CITATIONSIMPORT):   infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv' delimiter=',' MISSOVER 
 DSD firstobs=2;
 MPRINT(CITATIONSIMPORT):   informat "Ticket number"N $32.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Date anydtdtm40.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Time $32.;
 MPRINT(CITATIONSIMPORT):   informat "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   informat "Marked Time"N best32.;
 MPRINT(CITATIONSIMPORT):   informat RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   informat "Plate Expiry Date"N best32.;
 MPRINT(CITATIONSIMPORT):   informat VIN $1.;
 MPRINT(CITATIONSIMPORT):   informat Make $4.;
 MPRINT(CITATIONSIMPORT):   informat "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   informat Color $2.;
 MPRINT(CITATIONSIMPORT):   informat Location $16.;
 MPRINT(CITATIONSIMPORT):   informat Route $32.;
 MPRINT(CITATIONSIMPORT):   informat Agency best32.;
 MPRINT(CITATIONSIMPORT):   informat "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   informat "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   informat Fine_amount best32.;
 MPRINT(CITATIONSIMPORT):   informat Latitude best32.;
 MPRINT(CITATIONSIMPORT):   informat Longitude best32.;
 MPRINT(CITATIONSIMPORT):   format "Ticket number"N $12.;
 MPRINT(CITATIONSIMPORT):   format Issue_Date datetime.;
 MPRINT(CITATIONSIMPORT):   format Issue_Time $12.;
 MPRINT(CITATIONSIMPORT):   format "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   format "Marked Time"N best12.;
 MPRINT(CITATIONSIMPORT):   format RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   format "Plate Expiry Date"N best12.;
 MPRINT(CITATIONSIMPORT):   format VIN $1.;
 MPRINT(CITATIONSIMPORT):   format Make $4.;
 MPRINT(CITATIONSIMPORT):   format "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   format Color $2.;
 MPRINT(CITATIONSIMPORT):   format Location $16.;
 MPRINT(CITATIONSIMPORT):   format Route $12.;
 MPRINT(CITATIONSIMPORT):   format Agency best12.;
 MPRINT(CITATIONSIMPORT):   format "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   format "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   format Fine_amount best12.;
 MPRINT(CITATIONSIMPORT):   format Latitude best12.;
 MPRINT(CITATIONSIMPORT):   format Longitude best12.;
 MPRINT(CITATIONSIMPORT):   input "Ticket number"N Issue_Date Issue_Time "Meter Id"N $ "Marked Time"N RP_State_Plate $ "Plate Expiry 
 Date"N VIN $ Make $ "Body Style"N $ Color $ Location $ Route Agency "Violation code"N $ "Violation Description"N $ Fine_amount 
 Latitude Longitude;
 MPRINT(CITATIONSIMPORT):   if _ERROR_ then call symputx('_EFIERR_', 1);
 MPRINT(CITATIONSIMPORT):   run;
 
 NOTE: The infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv' is:
       Filename=/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv,
       Owner Name=u42020802,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=14Jan2020:07:19:42,
       File Size (bytes)=121516107
 
 NOTE: 799052 records were read from the infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations4.csv'.
       The minimum record length was 64.
       The maximum record length was 190.
 NOTE: The data set WORK.IMPORT4 has 799052 observations and 19 variables.
 NOTE: DATA statement used (Total process time):
       real time           1.85 seconds
       user cpu time       1.69 seconds
       system cpu time     0.17 seconds
       memory              2091.71k
       OS Memory           30632.00k
       Timestamp           01/19/2020 08:51:16 AM
       Step Count                        44  Switch Count  2
       Page Faults                       0
       Page Reclaims                     130
       Page Swaps                        0
       Voluntary Context Switches        25
       Involuntary Context Switches      2
       Block Input Operations            0
       Block Output Operations           362760
       
 
 MLOGIC(CITATIONSIMPORT):  Ending execution.
 142        %put %citationsimport (exportname=import5,
 142      ! infiledataset='/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv');
 MLOGIC(CITATIONSIMPORT):  Beginning execution.
 MLOGIC(CITATIONSIMPORT):  Parameter EXPORTNAME has value import5
 MLOGIC(CITATIONSIMPORT):  Parameter INFILEDATASET has value 
       '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv'
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv'
 FILENAME '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv'
 SYMBOLGEN:  Macro variable EXPORTNAME resolves to import5
 MPRINT(CITATIONSIMPORT):   data import5;
 MLOGIC(CITATIONSIMPORT):  %LET (variable name is _EFIERR_)
 SYMBOLGEN:  Macro variable INFILEDATASET resolves to '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv'
 MPRINT(CITATIONSIMPORT):   infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv' delimiter=',' MISSOVER 
 DSD firstobs=2;
 MPRINT(CITATIONSIMPORT):   informat "Ticket number"N $32.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Date anydtdtm40.;
 MPRINT(CITATIONSIMPORT):   informat Issue_Time $32.;
 MPRINT(CITATIONSIMPORT):   informat "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   informat "Marked Time"N best32.;
 MPRINT(CITATIONSIMPORT):   informat RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   informat "Plate Expiry Date"N best32.;
 MPRINT(CITATIONSIMPORT):   informat VIN $1.;
 MPRINT(CITATIONSIMPORT):   informat Make $4.;
 MPRINT(CITATIONSIMPORT):   informat "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   informat Color $2.;
 MPRINT(CITATIONSIMPORT):   informat Location $16.;
 MPRINT(CITATIONSIMPORT):   informat Route $32.;
 MPRINT(CITATIONSIMPORT):   informat Agency best32.;
 MPRINT(CITATIONSIMPORT):   informat "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   informat "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   informat Fine_amount best32.;
 MPRINT(CITATIONSIMPORT):   informat Latitude best32.;
 MPRINT(CITATIONSIMPORT):   informat Longitude best32.;
 MPRINT(CITATIONSIMPORT):   format "Ticket number"N $12.;
 MPRINT(CITATIONSIMPORT):   format Issue_Date datetime.;
 MPRINT(CITATIONSIMPORT):   format Issue_Time $12.;
 MPRINT(CITATIONSIMPORT):   format "Meter Id"N $6.;
 MPRINT(CITATIONSIMPORT):   format "Marked Time"N best12.;
 MPRINT(CITATIONSIMPORT):   format RP_State_Plate $2.;
 MPRINT(CITATIONSIMPORT):   format "Plate Expiry Date"N best12.;
 MPRINT(CITATIONSIMPORT):   format VIN $1.;
 MPRINT(CITATIONSIMPORT):   format Make $4.;
 MPRINT(CITATIONSIMPORT):   format "Body Style"N $2.;
 MPRINT(CITATIONSIMPORT):   format Color $2.;
 MPRINT(CITATIONSIMPORT):   format Location $16.;
 MPRINT(CITATIONSIMPORT):   format Route $12.;
 MPRINT(CITATIONSIMPORT):   format Agency best12.;
 MPRINT(CITATIONSIMPORT):   format "Violation code"N $20.;
 MPRINT(CITATIONSIMPORT):   format "Violation Description"N $22.;
 MPRINT(CITATIONSIMPORT):   format Fine_amount best12.;
 MPRINT(CITATIONSIMPORT):   format Latitude best12.;
 MPRINT(CITATIONSIMPORT):   format Longitude best12.;
 MPRINT(CITATIONSIMPORT):   input "Ticket number"N Issue_Date Issue_Time "Meter Id"N $ "Marked Time"N RP_State_Plate $ "Plate Expiry 
 Date"N VIN $ Make $ "Body Style"N $ Color $ Location $ Route Agency "Violation code"N $ "Violation Description"N $ Fine_amount 
 Latitude Longitude;
 MPRINT(CITATIONSIMPORT):   if _ERROR_ then call symputx('_EFIERR_', 1);
 MPRINT(CITATIONSIMPORT):   run;
 
 NOTE: The infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv' is:
       Filename=/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv,
       Owner Name=u42020802,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=14Jan2020:07:19:14,
       File Size (bytes)=293636484
 
 NOTE: 2000000 records were read from the infile '/home/u42020802/sasuser.v94/HSPRG/ParkingCitations/parkingcitations5.csv'.
       The minimum record length was 58.
       The maximum record length was 191.
 NOTE: The data set WORK.IMPORT5 has 2000000 observations and 19 variables.
 NOTE: DATA statement used (Total process time):
       real time           4.55 seconds
       user cpu time       4.18 seconds
       system cpu time     0.38 seconds
       memory              2091.96k
       OS Memory           30632.00k
       Timestamp           01/19/2020 08:51:21 AM
       Step Count                        45  Switch Count  2
       Page Faults                       0
       Page Reclaims                     130
       Page Swaps                        0
       Voluntary Context Switches        14
       Involuntary Context Switches      5
       Block Input Operations            0
       Block Output Operations           908040
       
 
 MLOGIC(CITATIONSIMPORT):  Ending execution.
 143        /*==========================================================================================================*/
 144        /*                           Data editing and managing                                  */
 145        /*Using Proc SQL improves run time and effeciency with such a large data set.                               */
 146        /*Producing multiple frequencys                                                        */
 147        /*==========================================================================================================*/
 148        proc sql prompt;
 149        create table citationstemp AS SELECT *
 150        FROM work.import1
 151        OUTER UNION corresponding
 152        SELECT *
 153        FROM work.import2
 154        OUTER UNION corresponding
 155        SELECT *
 156        FROM work.import3
 157        OUTER UNION corresponding
 158        SELECT *
 159        FROM work.import4
 160        OUTER UNION corresponding
 161        SELECT *
 162        FROM work.import5;
 NOTE: Table WORK.CITATIONSTEMP created, with 8799051 rows and 19 columns.
 
 163        create table citationstotal as select *, stnamel(RP_State_Plate) as state length=54,
 164         Issue_Date*86400 + input(put(Issue_Time,z4.),hhmmss4.) as issue_dttm format=datetime20. from work.citationstemp where
 164      ! not missing(Fine_amount) order by Issue_Date;
 ERROR: Numeric format Z in PUT function requires a numeric argument.
 ERROR: Numeric format Z in PUT function requires a numeric argument.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 165          
 166         select "Violation Description"N, count(*) as frequency from work.citationstotal group by "Violation Description"N order
 166      !  by frequency desc;
 ERROR: File WORK.CITATIONSTOTAL.DATA does not exist.
 167         select "Violation code"N, count(*) as frequency from work.citationstotal group by "Violation code"N order by frequency
 167      ! desc;
 ERROR: File WORK.CITATIONSTOTAL.DATA does not exist.
 168         select state, count(*) as frequency from work.citationstotal group by state order by frequency desc;
 ERROR: File WORK.CITATIONSTOTAL.DATA does not exist.
 169         create table citationsdate as select Issue_Date, count(*) as frequency from work.citationstotal where not
 169      ! missing(Issue_Date) group by Issue_Date order by Issue_Date  ;
 NOTE: Statement not executed due to NOEXEC option.
 170         select * from work.citationstotal (obs=100) where not missing(issue_dttm) ;
 ERROR: File WORK.CITATIONSTOTAL.DATA does not exist.
 171         create table citationsmonthyear as select *, month(datepart(Issue_Date)) as month, year(datepart(Issue_Date)) as year
 171      ! from work.citationstotal where not missing(Issue_Date) order by Issue_Date;
 NOTE: Statement not executed due to NOEXEC option.
 172         select month, count(*) as frequency from work.citationsmonthyear  group by month order by month;
 ERROR: File WORK.CITATIONSMONTHYEAR.DATA does not exist.
 173         select year, count(*) as frequency from work.citationsmonthyear  group by year order by year;
 ERROR: File WORK.CITATIONSMONTHYEAR.DATA does not exist.
 174        quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           12.36 seconds
       user cpu time       10.50 seconds
       system cpu time     1.87 seconds
       memory              13839.50k
       OS Memory           43452.00k
       Timestamp           01/19/2020 08:51:33 AM
       Step Count                        46  Switch Count  29
       Page Faults                       0
       Page Reclaims                     2349
       Page Swaps                        0
       Voluntary Context Switches        85
       Involuntary Context Switches      14
       Block Input Operations            0
       Block Output Operations           3994120
       
 175        
 176        
 177        /*==========================================================================================================*/
 178        /*                           Data graphs and such                                      */
 179        /*==========================================================================================================*/
 180        ods graphics on /  width=8in height=5in;
 181        title 'Citations Reported in 2014';
 
 
 182        proc sgplot data=work.citationsdate(where= (datepart(Issue_Date) >= '01JAN14'd and datepart(Issue_Date) < '01JAN15'd));
 ERROR: File WORK.CITATIONSDATE.DATA does not exist.
 183        series x=Issue_Date y=frequency /  legendlabel="Maximum Number citations";
 ERROR: No data set open to look up variables.
 ERROR: No data set open to look up variables.
 184        yaxis label="Number of Citations" valuesformat=comma6.;
 185        xaxis label="Date";
 186        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SGPLOT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              244.34k
       OS Memory           30372.00k
       Timestamp           01/19/2020 08:51:33 AM
       Step Count                        47  Switch Count  0
       Page Faults                       0
       Page Reclaims                     17
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 187        title 'Citations Reported in 2015';
 
 
 188        proc sgplot data=work.citationsdate(where= (datepart(Issue_Date) >= '01JAN15'd and datepart(Issue_Date) < '01JAN16'd));
 ERROR: File WORK.CITATIONSDATE.DATA does not exist.
 189        series x=Issue_Date y=frequency /  legendlabel="Maximum Number citations";
 ERROR: No data set open to look up variables.
 ERROR: No data set open to look up variables.
 190        yaxis label="Number of Citations" valuesformat=comma6.;
 191        xaxis label="Date";
 192        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SGPLOT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              248.43k
       OS Memory           30372.00k
       Timestamp           01/19/2020 08:51:33 AM
       Step Count                        48  Switch Count  0
       Page Faults                       0
       Page Reclaims                     17
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 193        title 'Citations Reported in 2016';
 
 
 194        proc sgplot data=work.citationsdate(where= (datepart(Issue_Date) >= '01JAN16'd and datepart(Issue_Date) < '01JAN17'd));
 ERROR: File WORK.CITATIONSDATE.DATA does not exist.
 195        series x=Issue_Date y=frequency /  legendlabel="Maximum Number citations";
 ERROR: No data set open to look up variables.
 ERROR: No data set open to look up variables.
 196        yaxis label="Number of Citations" valuesformat=comma6.;
 197        xaxis label="Date";
 198        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SGPLOT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              251.46k
       OS Memory           30372.00k
       Timestamp           01/19/2020 08:51:33 AM
       Step Count                        49  Switch Count  0
       Page Faults                       0
       Page Reclaims                     17
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 199        title 'Citations Reported in 2017';
 
 
 200        proc sgplot data=work.citationsdate(where= (datepart(Issue_Date) >= '01JAN17'd and datepart(Issue_Date) < '01JAN18'd));
 ERROR: File WORK.CITATIONSDATE.DATA does not exist.
 201        series x=Issue_Date y=frequency /  legendlabel="Maximum Number citations";
 ERROR: No data set open to look up variables.
 ERROR: No data set open to look up variables.
 202        yaxis label="Number of Citations" valuesformat=comma6.;
 203        xaxis label="Date";
 204        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SGPLOT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              251.46k
       OS Memory           30372.00k
       Timestamp           01/19/2020 08:51:33 AM
       Step Count                        50  Switch Count  0
       Page Faults                       0
       Page Reclaims                     17
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 205        title 'Citations Reported in 2018';
 
 
 206        proc sgplot data=work.citationsdate(where= (datepart(Issue_Date) >= '01JAN18'd and datepart(Issue_Date) < '01JAN19'd));
 ERROR: File WORK.CITATIONSDATE.DATA does not exist.
 207        series x=Issue_Date y=frequency /  legendlabel="Maximum Number citations";
 ERROR: No data set open to look up variables.
 ERROR: No data set open to look up variables.
 208        yaxis label="Number of Citations" valuesformat=comma6.;
 209        xaxis label="Date";
 210        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SGPLOT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              251.46k
       OS Memory           30372.00k
       Timestamp           01/19/2020 08:51:33 AM
       Step Count                        51  Switch Count  0
       Page Faults                       0
       Page Reclaims                     17
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 211        title 'Citations Reported in 2019';
 
 
 212        proc sgplot data=work.citationsdate(where= (datepart(Issue_Date) >= '01JAN19'd));
 ERROR: File WORK.CITATIONSDATE.DATA does not exist.
 213        series x=Issue_Date y=frequency /  legendlabel="Maximum Number citations";
 ERROR: No data set open to look up variables.
 ERROR: No data set open to look up variables.
 214        yaxis label="Number of Citations" valuesformat=comma6.;
 215        xaxis label="Date";
 216        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SGPLOT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              239.78k
       OS Memory           30372.00k
       Timestamp           01/19/2020 08:51:33 AM
       Step Count                        52  Switch Count  0
       Page Faults                       0
       Page Reclaims                     17
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 217        
 218        
 219        
 220        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to GOPTIONS NOACCESSIBLE;
 231

Here you go, it is kinda messy. Sorry

Kurt_Bremser · Posted 01-19-2020 04:30 AM

So your issue_date is not a date, but a datetime value. Omit the multiplication by 86400.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

E_money12 · Posted 01-19-2020 02:41 PM

Thank You, it now works properly.

Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Re: Appling STNAME Function in PROC SQL and other formats

Register Today!