I have a dataset data LVG1; set LVG; by ln_no; if comm_cd = 'CPBFOR' then CPBFOR=comm_dt ; if comm_cd = 'SNTLVG' then SNTLVG=comm_dt ; if comm_cd = 'UPBFOR' then UPBFOR=comm_dt ; if ltr_id =  'RG901' then RG901=ltr_dt; if ltr_id =  'RG902' then RG902=ltr_dt; if ltr_id =  'RG903' then RG903=ltr_dt; if ltr_id =  'CK007' then CK007=ltr_dt; if ltr_id =  'CK009' then CK009=ltr_dt; /*if SNTLVG >=d_LM_ACTIVATION_DT;*/ format CPBFOR SNTLVG UPBFOR RG901 RG902 RG903 CK007 CK009  mmddyy10.; run; I produced a proc transpose to capture the ln_no once and the individual comm_cd and ltr_id horizontally proc transpose data= lvg1 out=lvg2 by ln_no; var CPBFOR SNTLVG UPBFOR RG901 RG902 RG903 CK007 CK009  ;    run; By default the proc transpose produces the ln_no and columns.  Even though I have identified 8 vars,  I only get 4 columns in my proc transpose.  Why am  only getting 4 columns when I have identified 8 variables (var) ... View more

data test(rename=(rename=('A Category'n=A_Category);  rename=('B Category'n=B_Category)???? set mytable; run; I want to change the name of several variables.  Will I need a separate datastep for all?  FOr example I want to rename both A and B Category ... View more

I will pull a spreadsheet each month from an outside source.  Their program requires the following:  Account# I had been using the following to show the ln_no however I am getting an Account_ unitialized.  This is because the dataset I import in shows Account#. 1.  Is there a way to rename the Account# even though sas evaluates this differently and not as a text 2.  Also the account# needs to be a character and needs to show leading zeros if it is less than 10.  Some of the account numbers show more than 10 (ie 9.4201471E12 or 88089745000) data loans2;   length ln_no $10;   set loans;      if Account__ = . then ln_no = ' '; else ln_no = put(input(Account__,??10.),z10.); ... View more

data task; set task2; obs = _N_; run; This will assign the variable OBS and increment each row.  Let's say that if a loan number is listed twice (say row 12 and row 13) I want to increment once or show both as the 12th record. I want an increment based on the change in a loan number Loan_Number                                 OBS 111222                                            1 112333                                            2 112333                                            2 231111                                            3 143111                                            4 333333                                            5 333333                                            5 How could I modify the code to accomplish this ... View more

I have totalled a column of numbers ID         amt            difference 11        -250           125 11         125           125 /*only want to show this number*/ 12       -250          -250 proc sql; create table test as select id, sum( case when reg_num = 'w' then amt         when reg_num = 'y' then amt    else 0 end)as difference from main group by ln_no; quit; In the case of ID 11, I want to show the 125 difference in the second instance of the same ID.  So I want to show ID on row 2 and leave row 1 blank.  In the case of ID 12 I want to show the difference because it is the first and only instance. ... View more

proc sql; create table test; select payee_id, corp_payee_id, cash_adv_am,reason_cd from adv_table group by payee_id,corp_payee_id,reason_cd quit; sample output payee_id          corp_payee_id           cashadv_am            reason_cd 111                  A_3                          125                          n                        B_3                          250                          r 112                 A_3                          125                          n                      B_3                           250                         r I want to execute a difference grouped as above so I want to add a total.  So 125-250 should give a negative number by group.  Here is the desired results: payee_id          corp_payee_id           cashadv_am            reason_cd        difference 111                  A_3                          125                          n                        B_3                          250                          r                                                                                                              -125 112                 A_3                          125                          n                      B_3                           250                         r                                                                                                              -125 If easier the difference can be placed as a running total directly in the cashadv_am field. How would I get a rollup totalling to work.  Should this be done using a having clause in the proc sql ... View more

The following data choices are listed in the workclass field in the table loss_payments: "Retention" "Liquidation" Here is an example: proc summary data=loss_payments sum nway;    where work_class = 'Retention';    class iv_grp work_status;    var age_grp:;    output out=retention(drop=_type_ rename=(_freq_=total)) sum=; run; data retention_pct;    set retention;       array age_grp[5];    array pct[5];    do i=1 to 5;       if total then pct = round(age_grp/total,.0001);    end; run; proc sort data=retention_pct;   by iv_grp descending workout_status; run; I need to take two records that appear in the results, Prep1 and Prep2 and create a dummy work_class called "Prep". I then need to do a proc summary on those records that are Prep1 or Prep2 following the logic above.  So work_class = 'Retention' is replaced by work_class = 'Prep'. ... View more

data decision3;   set decision2a;   by ln_no; ***Streamline***;     if template_NM IN ('STR1','STR2') then do;      if DEN_REASON_CD in ('DI','II','PM','NH','TI','NV','BR','IN','ID','IM','ER') then denial_count = 0.50;     if trial_count = 1 then do; trial = 0.50; approval = 0.50; total = 0.50; final = 0.50; denial = 0.50; end;      if final_count > 0 then do; final = 0.50; approval = 0.50; total = 0.50; trial = 0; denial = 0.50; end;      if denial_count = 1 then do; denial = 0.50; approval = 0.50; total = 0.50; trial = 0.50; final = 0.50; end;   end;   else do;    ***Not Streamline***;   if template not IN ('STR1','STR2') then do;   if  LM_REFERRAL_CD in ('TD3','4BN') then do;     if DEN_REASON_CD in ('DI','II','PM','NH','TI','NV','BR','IN','ID','IM','ER') then denial_count = 1;     if trial_count = 1 then do; trial = 0.25; approval = 0.25; total = 0.25; final = 0; denial = 0; end;      if final_count > 0 then do; final = 0.25; approval = 0.25; total = 0.25; trial = 0; denial = 0; end;      if denial_count = 1 then do; denial = 0.25; approval = 0; total = 0.25; trial = 0; final = 0; end;   end;   else do;     if trial_count = 1 then do; trial = 1; approval = 1; total = 1; final = 0; denial = 0; end;     if final_count > 0 then do; final = final_count; approval = final; total = final_count; trial = 0; denial = 0; end;     if denial_count = 1 then do; denial = 1; approval = 0; total = 1; trial = 0; final = 0; end;   end;   if approval > 0 then LM_DEN_REASON_CD = ' ';   PROC_STRING = strip(put(LM_REFERRAL_CD,$amname.)) || " - " || strip(LM_REFERRAL_CD);   drop trial_count final_count denial_count; end; run; proc sort data=decision3;   by LM_REFERRAL_CD ln_no; run; By chance do you see anything wrong with the code.  I keep getting error below. It appears I have my do; and end; accounted for but it shows the error after run; twice yet in my code I show run; once. 485        486          drop trial_count final_count denial_count;end; 487        488        run; 488        run;               _               117 ERROR 117-185: There was 1 unclosed DO block. It looks like the problem occurs toward the end of the program.  The code only shows run; once but shows it twice in the error message ... View more

data test; if total = -0 then R=0; run; This did not convert -0 to 0; I tried this also data test; if total = 0 then R = abs(val0); run; ... View more

I admit I have not utilized spanning headers and color coding columns in my short SAS career. Here is a sample of my desired results.  I want to color code entire columns with colors based on the data.  I attempt to plug in Details 1 through 3 in the code.  Essentially the entire columns need to be color coded with spanning headers based on the logic I have below.  I am getting error msgs so thought I would share with the group   Detail1 Detail2 Detail3 Loan_Num L_Name Employer Salary Region Hire_Date 1122 Smith Comp1 33000 West 5/1/1999 1123 Smith Comp2 33001 West 5/2/1999 proc report data = test2; colums Loan_num ('^S={Background=Blue} Detail1' )           L_Name('^S={Background=Blue} Detail1' )           Employer('^S={Background=Orange} Detail2' )         Salary('^S={Background=Orange} Detail2' )       Region('^S={Background=Green} Detail3' )         Hire_Date('^S={Background=Green} Detail3' ); run; ... View more

I have a report that writes to ODS Output. Example COLUMN                      ID  LAST_NAME  FIRST_NAME    WEST    EAST   SOUTH Sample Output              1   SMITH           JOHN                  Y                       Y Now I need to show a row on top and color code the last three variables based on name Sample Desired                               MY FIRST ROW                                       Detail                                  ZONE1   ZONE2    ZONE3 COLUMN               ID  LAST_NAME  FIRST_NAME    WEST      EAST     SOUTH                                   1   SMITH           JOHN                  Y                              Y The DETAIL area should not change.  The Column and header is already blue Here is the present code for the style style(report)=[background=black cellspacing=10 just=center font_size=11pt font_face="Calibri" bordercolor=black borderwidth=1] style(column)=[background=white font_size=10pt bordercolor=black borderwidth=1] style(header)=[background=blue font_weight=bold foreground=white bordercolor=black borderwidth=1] ; MY FIRST ROW should be the first row header DETAIL  ZONE1 2 and 3 should be the second row headers The ZONE1 should have a header(WEST) and row header=GREEN The ZONE2 should have a header(EAST) and row header=RED The ZONE3 should have a header(SOUTH) and row header=BLUE ... View more

  I wrote a proc sql to pull in ID, NAME, LS_DATE1 through 4 proc sql; create table Test as select ID,NAME,LS_DATE1,LS_DATE2.........etc from my_table; quit; Some rows will have the LS_DATES populated (Not All).  Each row will usually only have one date in one of the LS_DATE columns.  If there are multiple dates in multiple columns in the same row, I want the last date that appears. I want to get the date and place it in its own GET_DATE column with the same value.  I will use the GET_DATE in calulating the difference in days later. So I need two examples, one that assumes only one date per column per row and another that looks for the last date in a row if there are multiples. SAMPLE ID                                      NAME               LS_DATE1            LS_DATE2             LS_DATE3             LS_DATE4 1                                      Smith                 4/15/12 2                                      Bryant                                              1/12/13 3                                      Jones                  5/1/13 4                                     Thomas                                                                            12/13/06 ... View more