You can use the cntlout= option to copy all existing formats to a SAS dataset.  This can be distributed for review or stored as a backup.  It can be exported to Excel for amendment and then imported back into SAS.  There are a lot of columns but the main ones to review are called start and end, the start and end values for a range covered by a single formatted value.  Additional rows can be entered if required.  The modified table can then be read back into proc format using cntlin=.  Make sure no-one has renamed any of the columns. Proc format cntlin=work.formatlist ; quit ; You can also create a cntlin table to add a new format into the library, from a lookup table in the database.  At a minimum you need to define s start column, a label column to hold the formatted value, and a fmtname column as the name of the format.  A HLO column is often useful to define how the format will handle out of range values. Richard ... View more

Charlotte These seem to be the facts Oracle has a (nearly?) complete set of job descriptions and department codes, but no level information Your Excel spreadsheet contains an outdated or incomplete set of codes for company, department, level and position, but crucially not the full job description that would match what is in Oracle (or does it?) You simply do not have enough information to proceed with an automated derivation of job codes that could be made consistent with what is already in Excel (and what I suspect is being used downstream in reporting).  The only possible join is on Department code, if indeed that is consistent, and that does not have enough granularity to match existing position codes with Job Designations.  You could set aside the excel version and just produce new codes from the Oracle extract, treating the Level information as missing.  Then try and match the new version with the excel table, using it to update Level information for those jobs where the new job code matches the existing excel position code.  I'm guessing there will be mismatches which will cause havoc with downstream processing, which might be alleviated by keeping everyone informed. If the spreadsheet did contain job descriptions (as in Oracle) you could make some further progress and be more consistent with existing codes.  You could use existing position codes where they matched job descriptions in Oracle, and code of the type I have suggested to create new ones as required.  You could fill in Level information where it exists, and leave the rest blank.  The rest would have to wait for a downstream project. It may not be too big a job to manually match Oracle job designations to the existing codes.  A company of 700 is unlikely to have 700 unique job designations, so if you sort both tables by department code and job or position and put them side by side many matches will be obvious.  It may even be quicker at this point to create new position codes for jobs that are missing form excel.  If you can do this you will have a lookup table that can convert Oracle job designation and Department to company, department code, (partially to level), and position code; which may be all you could hope for in the short term. Richard ... View more

From what we can see the large tables are likely to be located in an enterprise RDBMS and to follow on DBailey's suggestion you might consider using explicit passthrough on the inner join as an uncorrelated subquery, and perform the left join on the SAS side. Do you really need all the variables from largetb1 in your result set?  Reducing the size of the output vector should improve write performance (eg key is probably redundant after the left join is performed).  Why do you select both s.vi (implied) and c.v1 if they are there as a result of an equi-join?  And if SAS must perform the SQL, why keep v2 and v3 from largetb2 if they play no part in the logic and the output? We can't tell from the information whether the output dataset has 30 million records (a many to one join), 2 million records (one to one, excluding non matches), or some other number.  The SAS optimiser has evidently determined that sort merges  are required (ie it will not use the indexes).  You might be able to avoid two of the sorts by creating formats from the small table. Richard ... View more

Charlotte I thought after I sent my last response I had not thought out the department/level: as you have indicated the departments are probably single words.  I suspect that level information is what may be missing in some departments, so you can substitute ANY, ALL, NUL, or XXX or whatever because you will need a placeholder in your code. First get from your Oracle system a complete list of Departments and management levels in each.  If you have the list in a spreadsheet with those 2 columns (all levels for each department) you can decide how department and level are to be encoded and expand the table to 4 columns to include codes for department and level: Department     Management_Level     Dept     Lvl Finance          Senior Management     FIN      SNR Finance          Middle Management     FIN     MID Finance          Lower Management     FIN      LWR Technology     Senior Management     TXG      SNR Technology     Middle Management    TXG     MID Technology     Lower Management      TXG     LWR Marketing     Senior Management     MKT      SNR ... etc Research                                        RES     XXX ... etc Import or create this table into SAS as Departments. Next get an extract from Oracle of all positions, with columns for Department, Level, and Position.  I'll assume that's called Extract.  Now we can join these two files to bring the codes for Dept and Lvl together with the position descriptions, and get it all sorted. Proc SQL ;      Create Table Bigfile as           Select  Distinct                       X.Department                ,     X.Level                ,     X.Position                ,     D.Dept                ,      Case                          When X.Level IS NULL                               then 'XXX'                          Else D.Lvl                           End        As Lvl           From     Extract     X                Left Join                        Departments D                 On     D.Department     =     X.Department                And     D.Management_Level = X.Level           Order by   4, 5           ; Quit ; Dump that table into Excel and look for mismatches in Department names or Management levels; if necessary update your Department Table and run the SQL again.  If all is good, you can proceed to the next step which is a cut down version of my previous attempt.  NB use dashes or underscores as required. Data Job_Codes ;      Set Bigfile ;      By Dept Lvl Position ;      Length Pos Pos0 $3           job_code $16           k $1 ;      Retain     Cmp    'XYZ'                      Pos0     ' '                     ;      Do i = 1 to 3;           k = ' ' ;           k = Upcase (Scan (Position, i)) ; /* Initial letter */           Substr (Pos, i, 1) = k ; /* Stuff it into the Position code */      End ;      Pos0 = Lag (Pos) ;      If Pos = Pos0 then _ERROR_ ; /* unintended duplicates*/      job_code = catx ('-', cmp, dpt, lvl, pos) ;      Drop  Pos0 i k ; Run ; The _ERROR_ statement is intended to catch you eye if there are any duplicate position codes within a department and level. I haven't tested this code so if you have any problems please provide an extract from the log. Richard Message was edited by: Richard Carson (missing End statement in SQL) ... View more

Charlotte Regarding the sun - as we are heading into winter now we have about as much chance of seeing it as in the UK this time of the year! I have the feeling you are being asked to rectify a defect in organisational planning (nothing new in that, except usually technical staff are the last to be consulted).  Normally I would expect job codes to be assigned by HR as part of setting up the job descriptions, and these codes would be entered into your database; and it would be the task of the reporting analyst to translate those job codes back into job titles.  In this case the titles are already available but someone now feels the need to inflict codes on the organisation. Job and department codes really only serve two useful purposes:  saving space in a computer database (not in your case - the database contains the full title) and saving space in financial articles.  Hence everybody recognises codes such as CEO, CFO, COO and the like and the become status symbols for the elite, like military decorations (DSO, VC etc).  The trouble is that lower down the organisational pyramid there are potentially overlapping codes for quite dissimilar roles.  SA, for example, can be Systems Architect, Senior Analyst, or Sales Assistant.  Hence the idea of attaching company-department codes to the job codes to distinguish between duplicates.  This is making your codes unwieldy.  By the way, if job codes were properly implemented in your organisation there would be a database table translating the codes into full titles, and a procedure for updating those tables; and you could use the methods others have suggested to expand codes into descriptions as required. However, if you have no choice to implement codes in the format you have described I would hope you have a source table with the following columns: Company, Department, Level, Position.  I changed the order as I think it is more logical to have Company-BD-SM-CFO. Proc Sort data = Job_Titles ;   By Company Department Level Position ; Run ; Data Job_Codes ; Set Job_Titles ; By Company Department Level Position ; Length Cmp Dpt Lvl Pos $3 Cmp0 Dpt0 Lvl0 Pos0 $3 job_code $16 k $1 ; Retain Cmp Dpt Lvl Cmp0 Dpt0 Lvl0 Pos0 ; If first.Company then Do ; Cmp0 = Cmp ; Cmp = ' ' ; Do i = 1 to 3; k = Upcase (Scan (Company, i)) ; /* Initial letter */ Substr (Cmp, i, 1) = k ; /* Stuff it into the company code */ End ; If Cmp = Cmp0 then put _All_ ; /* unintended duplicates*/ If first.Department … If first.Level … If first.Position … job_code = catx ('-', cmp, dpt, lvl, pos) ; Drop  Cmp Dpt Lvl Cmp0 Dpt0 Lvl0 Pos0 i k ; Run ; KiaOra (I'm really a displaced Kiwi) Richard ISW (Itinerant SAS Wrangler) ... View more

Charlotte From the examples you have given there does not seem to be any straightforward way of deducing the 'code' from the job designation. Also, you have assigned 'sm' to more than one position.  As for the shifting sands of fashionable job naming, none of us can guess where that might go next so that part of the problem is insoluble. Normally with a code you would have a short alpha numeric string unique to each description.  I would suggest you could have say 5 characters: up to the first 3 could be the initial letters of designation words and the rest a sequence number to avoid duplication.  So your example would become senior management                SM001 middle management                MM001 lower management                LM001 finance manager                    FM001 sales manager                        SM002 marketing manager                MM002 strategy consultant            SC001 communications manager        CM001 technical enginner            TE001 support engineer                SE001 logistics                                L0001 supply chain manager            SCM01 brand manager                        BM001 location manager                LM002 Here is code  (untested) which should achieve the result proc sort data = designations ;      by job_name ; run ; data job_code ;      set designations ;      length k $1                prefix $3                code $5                ;      retain seq 0 ;      by job_name ;      if first.job_name ;      prefix = '000'      do i = 1 to 3 ;           k = upcase (scan (job_name, i)) ;           if k > ' ' then           substr (prefix, i, 1) = k ;      end ;      prev = lag (prefix) ;      if prefix = prev then seq + 1 ;      else seq = 1 ;      code = prefix || put (seq, z2.) ;      keep job_name code ; run ; There is a problem with this approach:  supposing a new designation is encountered you cannot simply re-run the code because existing codes might be shifted to new positions.  Code for an automatic update could be quite complex; it might be easier to deal with updates manually as they are encountered. A way round this would be to make the sequence number equal to _N_, the row number; then any new code would take the next highest sequence number.  You might need to allocate more than 2 sequence numerals. Gidday from Oz Richard ... View more

Proc SQL Noprint ;      select name into :keeplist separated by ' '      ; Quit ; Data Data_Set_2a ;      Set Data_set_2 ;      Keep &keeplist ; Run ; Richard ... View more