Have you tried input(compress(b.GALC_ALRT_PHONE_NO,'','KD')) ?  'KD' will keep only numeric characters.  There may be other delimiters in your phone data. Richard in Oz ... View more

SQL with inner joins on 4 or more tables is perfectly feasible. You have to be careful that all the joins are stated, to avoid some sort of cartesian product. In your example you are joining the same table (gfgsfs) twice.  If this is intentional note that the columns represented by d.* are the same as those represented by c.*, and SAS will not allow duplicate column names.  You can rename them on the fly or as a ddataset option if need be. Richard in Oz ... View more

We only have one example of a resignation in the data so this suggestion is interim: First search for keywords such as President, Chief or Vice.  If not found, return 0 for both columns Probably exclude any row that has result in it Next look for the string Resig.  If found, assume it is a resignation Other wise assume it is an appointment I would suggest you need thousands of rows of data to fine tune the algorithms Richard in Oz ... View more

Update will always overwrite the phone number in A with a non missing value in B, but the OP wants to preserve existing non-numeric values in A.  In the past I have used update twice - first to create NewB which is updated with existing data from A, then updated A with NewB. Richard in Oz ... View more

The print option has no effect if you are creating a table.  PROC SQL will only print when you select but no table is created, eg Proc SQL ;      Select *      from have ; quit ; We use Proc SQL Noprint when we want to create a macto variable but do not need print output ; Proc SQL noprint ;      Select value           into :macvalue      from have ; quit ; Richard in Oz ... View more

In brief, you need a table called a SAS control table in a specified layout.  Then you just invoke      proc format cntlin = your_control_table ;      quit ; At a minimum the control table needs to have 3 columns which must be called start, label and fmtname.  The first is the initial value, the second is the formatted value, and the third is the name to be assigned to the format.  Values in the fmtname column are repeated for every row. You can create this table in SQL or in a datastep (imagining that the only possible marks are 0, 590, 60, 70, 80)      proc SQL ;           create table fmtcntl as                select  mark as start                     ,     grade as label                     ,     'gradefmt' as fmtname                from     grades_lkup ;      quit ;      proc format cntlin = fmtcntl ;      quit ; You can only have one cntlin table per proc format invocation, but the control table can have concatenated lists of lookups if each has thier own fmtname. If you want to create a character format you have to add an additional column called type which would hold the value 'C' for each row.  (The default is 'N' for a numeric format).  Fir a numeric informat, type = 'I'.  Other columns can hold additional information: eg end is the end value of a range.  You need that if your grade format has a continuous integer range.  Thus for your data (where end values have to be inferred) data fmtcntl ;      length       start     end     8     grade     $ 4 ;      retain      fmtname      'gradefmt' ;      set grades_lkup end = lastrow ;      start = lag (mark) ;      end = mark - 1 ;      label = lag (grade) ;      if    _N_ = 1        then return ;      output ;      if lastrow        then           do ;                start = mark ;                end   =  100 ;                label = grade ;                output ;           end ;      drop mark ; run ; This may seem more effort than is worth for a simple format, as in you example, but if you lookup table contains more than a few rows it can be worthwhile especially for lookup tables which are straight transformations. Richard in Oz ... View more

Have you tried running SAS Connect in "reverse"?  That is where the server becomes the client for a remote session on what you currently regard as your local machine.  You would then have 2 sessions on your PC and could control the exports by nesting rsubmits: Local1 => rsubmit to server running main extract and analysis => rsubmit and upload to Local2 for export Just a thought Richard in Oz ... View more

Christof Sorry if I was a bit harsh. There is a way to create functions in SAS, it is Proc FCMP (function compiler) but I think that's a more complex solution than you need.  Looking at your data, a simple SQL merge or left join would suffice, even for large tables.  You can also do a datastep merge but this requires both tables to be sorted by the common column.  Someone here may come up with a hash solution: I prefer the less convoluted approach. Another technique which might be closer to what you are trying to achieve is to create a format from the occupation data and apply the format to your contract table.  In this case you need an informat because the returned value is numeric.  To create the informat you have to give it a name and specify a type. libname temp 'C:\Users\Public\Temp' ; data occ_fmt ;      Set   temp.occuptaionlist2 ;      Retain    fmtname    'occcode'             type    'I'             ;     Rename    Occupation    =    start             group        =    label             ; run ; proc format cntlin    = occ_fmt ; quit ; data test ;     set     temp.contract_psm03 ;     group    =    input (PARTNER_VN_MBERUF, ?? occcode.) ; run ; The ?? in the input function returns a missing value without creating an error if there is no match. Richard in Oz ... View more

Here is a general solution to the problem you pose.  It is based on my suggested data table format for your previous question.  The code is appended. Program steps Create source table.  Using data supplied in your question.  Numbers for 2011 have been assigned to Q4 without affecting the outcome of this example. Year and Quarter values are stored as strings for simplicity and efficiency. Find last complete year.  This is the max value for year where data for Q4 exists.  The previous and next years are also calculated. (If no “next” year data exists, this will not disturb the process.) Summarise data.  We want summaries for the years in question, by region, CU, year and quarter.  Some of the data is redundant but will be screened out in the next step.  The _type_ variable indicates the level of summarisation. Add category and group labels.  Identify regional subtotals (ALL CU) and overall TOTALS per quarter and year.  Dummy YOY records are created as placeholders in the table.  The _name_ variable will be used for column headings by proc transpose. Sort into order for transposition.  The sort ensures subtotals and totals come last at regional and overall level. Transpose data into table form.  This spreads the selected quarterly and annual data across columns, instead of down rows. Calculate YOY and clean up.  Calculate the YOY as required, add the subtotal and total labels and get rid of unwanted columns for cat, grp, and _name_. Richard in Oz /*    Calculate_totals_YOY.sas   */ /*    Required calculation of region totals     and totals by quarter and year,     with year on year for the last     complete year  */     /*    Richard C Carson     Melbourne, Australia     richard@arcol-analytics.com     +61 412 836 040     */     /*    Step 1: Create source table   */ data have ;     infile datalines dsd dlm='09'x ;     informat   region_nm    $8.    cu_nm   $3.    year   $4.    quarter   $1.    number   best16.    ;     input   region_nm    $    cu_nm   $    year   $    quarter   $    number      ; datalines ; REGIONX 2011    4    14 REGIONX CUX    2011    4    3 REGIONX CUY    2011    4    4 REGIONX 2012    1    3 REGIONX CUX    2012    1    1 REGIONX CUY    2012    1    2 REGIONX 2012    2    4 REGIONX CUX    2012    2    2 REGIONX CUY    2012    2    2 REGIONX 2012    3    5 REGIONX CUX    2012    3    2 REGIONX CUY    2012    3    3 REGIONX 2012    4    6 REGIONX CUX    2012    4    0 REGIONX CUY    2012    4    0 REGIONX 2013    1    3 REGIONX CUX    2013    1    1 REGIONX CUY    2013    1    1 REGIONX 2013    2    4 REGIONX CUX    2013    2    2 REGIONX CUY    2013    2    0 ; /*    Step 2: Find last complete year   */ Proc SQL noprint ;     Select put(max(input(year,4.)),4.)    into    :year1     From    have     Where    quarter = '4'    ; Quit ; %Put    NOTE: Last complete year is &year1.. ; %Let    year0    =    %eval(&year1 - 1) ; %Let    year2    =    %eval(&year1 + 1) ; /*    Step 3: Summarise data    */ Proc Summary    missing    data    =    have ;     where year in ("&year0", "&year1", "&year2") ;     By   year ;     Class   region_nm    cu_nm    quarter    ;     var   number ;     Output     out   =    totals    sum     =    ; Run ; /*    Step 4: Add category and group labels    */ Data    totals2 ;     Length    _Name_    $ 8 ;     Set     totals ;     By    year ;     Where     (year = "&year0" and _type_ IN (0, 4, 6))       or     (year = "&year1")    or    (year = "&year2" and _type_ IN (1, 3, 5, 7))    ;     If    quarter > ' ' then _name_ = cat('Y', year, 'Q', quarter) ;       else    _name_ = cat('Y', year) ;        Select (_type_) ;    When (0,1)    cat    =    'TOTAL' ;    When (2,3)   delete ;    When (4,5)    grp    =    'ALL' ;    Otherwise ;     End ;     If   _name_    =    "Y&year1"       Then    Do ;    Output ;    _name_    =    CATT(_name_, 'YOY') ;    Call missing (number) ;    output ;    End ;       Else   output ;     Drop    _freq_ ; Run ; /*    Step 5: Sort into order for transposition     */ Proc Sort    Data    =    totals2 ;     By   cat    region_nm    grp    cu_nm    year    descending    _type_    quarter    ; Run ; /*    Step 6: Transpose data into table form   */ Proc Transpose    data    =    totals2    out   =    required    ;     By   cat    region_nm    grp       cu_nm      ;     Var   number ; Run ; /*    Step 7: Calculate YOY and clean up    */ Data    want ;     set   required ;     If    cat    > ' '       Then    region_nm    =    cat ;     If    grp    > ' '       Then    cu_nm    =    grp ;     Y&year1.YOY    =    SUM(Y&year1, -Y&year0) ;     Drop    cat    grp    _name_    ; Run ; ... View more

You say "I cannot change what the question requires. So will have to work with gender as outcome." I disagree.  Having gender as the outcome implies you have a population which undergoes sex change as it progresses through rank, academic outcome and salary. As an analyst you have a responsibility to challenge incorrect assumptions.  Otherwise you are little better than a 'script kiddie' throwing code at data in the hope that something sticks.  Who is asking the question?  Go back to them and get them to restate the problem. Richard in Oz ... View more

I recommend you store data in the following format region_nm cu_nm year quarter number REGIONX  2012 1 3 REGIONX  CUX 2012 1 1 REGIONX  CUY 2012 1 2 REGIONX  2012 2 4 REGIONX  CUX 2012 2 2 REGIONX  CUY 2012 2 2 REGIONX  2012 3 5 REGIONX  CUX 2012 3 2 REGIONX  CUY 2012 3 3 REGIONX  2012 4 6 REGIONX  CUX 2012 4 0 REGIONX  CUY 2012 4 0 With this structure you can do annual summaries by region_nm and cu_nm and transpose the results for your report. Richard in Oz ... View more

The answer depends on whether you want a solution that works for 2012 and can be adapted for subsequent years, or an automated solution tha twill automatically add in year totals as it detects new columns for the quarters. The former is simple.  You can use a datastep which will allow you to reuse the table name.  You will need to set the option that will recognise name literals (the non standard column names). Data results ;      Retain region_nm cu_nm '2011Y'N '2012Q1'N '2012Q2'N '2012Q3'N '2012Q4'N '2012Y'N '2011/2012YOY'N '2013Q1'N '2013Q2'N;      Set results ;      '2012Y'N = Sum ('2012Q1'N, '2012Q2'N, '2012Q3'N, '2012Q4'N) ;      '2011/2012YOY'N = '2012Y'N - '2011Y'N ; run ; I will suggest a better way of storing your data to remove the need to add columns in your database. Richard in Oz ... View more

A few initial remarks: Your use of the freq statement is incorrect.  You would only use it if you had n identical instances which were represented in one obsevation of your data, with a frequency of n. Modelling sex as if it were a dependent attribute is a bit perverse.  I would expect you to model rank or salary on some set of the other indicators.  To use logistic on salary you would have to segment the data, perhaps as low, mid or high. I'll leave the finer points to others. Richard in Oz ... View more