Hi SAS Forum, I have three accounts in this longitudinal dataset. This shows how accounts have aged over 4 months (note 0 is the first month). Variables TRANSIT, ACCTNUM and age together form an unique record. data have; input TRANSIT ACCTNUM age bal stat $ 14 -20; cards; 10 111 0 220 1-30 10 222 0 450 31-60 20 111 0 300 61+ 10 111 1 100 CURRENT 10 222 1 5   61+ 20 111 1 75  NPNA 10 111 2 100 CURRENT 10 222 2 300 CURRENT 20 111 2 100 DWO 10 111 3 110 1-30 10 222 3 320 61+ 20 111 3 105 DWO ; RUN; In transit 10, acctnum 111 started its life in age 0 with “1-30” stat (stat means status). This account then cycled into “CURRENT” stat when age = 1, and remained in the same stat when age=2 also but deteriorated into “1-30” stat when age=3. Question; I need to over write all the subsequent values in variable “stat” with “CURRENT”, after an account once hit “CURRENT”. So, the final data set I need is this: data want; input TRANSIT ACCTNUM age bal stat $ 14 -20; cards; 10 111 0 220 1-30 10 222 0 450 31-60 20 111 0 300 61+ 10 111 1 100 CURRENT 10 222 1 5   61+ 20 111 1 75  NPNA 10 111 2 100 CURRENT 10 222 2 300 CURRENT 20 111 2 100 DWO 10 111 3 110 CURRENT       /* in "have" dataset, this was 1-30”*/ 10 222 3 320 CURRENT       /*in "have" dataset, this was 61+*/ 20 111 3 105 DWO ; RUN; I HAVE tried THIS: /*sorting*/ proc sort data=have;   by transit acctnum age; run; /*Interlace stacking – Haikuo is acknowledged*/ data want;    set have (in=up) have;      by transit acctnum age;      if first.acctnum then call missing(flag);      if up and STAT='CURRENT' then flag+1;      if not up and flag>=1 then output;      drop flag; run; But I do not know how to tweak the above code to get the intended results (or create an alternative code). Could anyone help me? Thanks Mirisage ... View more

Hi SAS Forum, Scenario 1 I have imported a Access dataset (lets call Access_have_1) into SAS. Lets call the created SAS data set as have_1. Then just stacked it with an existing sas dataset called have_2. Then created want_1 sas dataset. data want_1; set have_1       have_2; run; Scenario 2 I have first exported the same Access_have_1 dataset above as a CSV file. Then read the CSV file into SAS and created the SAS data set named have_3. Then stacked like below. data want_2; set have_3       have_2; /*exisiting SAS dataset*/ run; Question: I got Want_1 and Want_2 files as identical two files. However, when I ran proc freq for a variable called "Arrears_history" in both files one keeps giving strange freq resutls. Scenario 1 gives this table. proc freq data =want_1; tables arrears_band/list missing; run; Arrears_Band Frequency 1 1 - 30 10 30 - 60 25 60 - 90 5 1 - 30 15 30 - 60 15 60 - 90 8 90 + 2 Current 400 NPNA 9 Scenario 2 gives this table. proc freq data =want_2; tables arrears_band/list missing; run; Arrears_Band Frequency 1 1 - 30 25 30 - 60 40 60 - 90 13 90 + 2 Current 400 NPNA 9 I keep trying several times still the same resutls are recurring. You will see blue color and red color in the table under scenario 1 adds to meroon color figures in scenario 2 table. Actually scenario 2 freq table is the correct one but how come scenario 1 table is strange. Your help is appreciated. Mirisage (p.s. I cannot provide the data set as they are several thousands) ... View more

Hi SAS Forum, I ran the following two macros for two products, i.e. mango and orange. MACRO I: %macro anyname(mth=, month=, product=); title "Distribution of values of Income variable in &product file in &month"; proc freq data=mango.&product._&mth; /*Why can't I change libname mango to &product*/   tables county;     run; %mend; %anyname(mth=Dec12, month=Dec2012, product=mango); %anyname(mth=Jan13, month=Jan2013, product=mango); %anyname(mth=Feb13, month=Feb2013, product=mango); %anyname(mth=Mar13, month=Mar2013, product=mango); MACRO 2: %macro anyname(mth=, month=, product=); title "Distribution of values of Income variable in &product file in &month"; proc freq data=orange.&product._&mth; /*Why can't I change orange to &orange*/    tables village ;     run; %mend; %anyname(mth=Dec12, month=Dec2012, product=orange); %anyname(mth=Jan13, month=Jan2013, product=orange); %anyname(mth=Feb13, month=Feb2013, product=orange); %anyname(mth=Mar13, month=Mar2013, product=orange); Background for Question; As Mango and Orange data are found in two different libraries, namely "Mango" and "Orange", it necessitated two macros, MACRO I and 2. I tried to macronize libnames too to avoid MACRO 2, but it doesn't seem working. Question: Can't we macronize libnames (as shown in red and pink colors above). Thank you for your help. Mirisage ... View more

Hi SAS Forum, I need to categorize the subjects of my large dataset into the cells indicated as xxxx in below table. This table's column variable is "Bal-band" while raw variable is 'beac_band".                             Bal_band Beac_band 3000+ 1001-3000 <1000 1-200 xxxx xxxx 201-610 xxxx 611-700 700+ 0 or none xxxx First, at the highest level subjects have to be filtered like this. if b_status = 'flcv' and sta_in_12 = '1-30' and use_band ='<100' then do; To create a flag_variable so that I can identify the subjects to be allocated into Cells of our intrest in the above table, I have tried the below code. data want; set have; if b_status = 'flcv' and sta_in_12 = '1-30' and use_band ='<100' then do;      if beac_band in ('1-200', ’201-600') and bal_band in ('3000+') or if beac_band in ('1-200') and bal_band in('1001-3000') or if beac_band in ('0 or none') and bal_band in('3000+') then Flag = 'Medium';      end;     if …………then do; …….. ……. End; un if …………then do; …….. ……. End; RUN; Question: An error messege comes like this. beac_band in ('580-620')                                                                                                        ___________                                                                                                        22 18       ! and bal_band in('3000+') then Fog = 'Medium'; ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,               IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.  Could you please let me know whether I can or cannot use the above code with several “or”  “or”   “or”. Thank you for your help. Mirisage ... View more

Hi SAS Community, I have two data files, cops and COL. Data cops; Input transit acctnum; Cards; 1 111 1 222 1 333 ; Run; Data COL; Input transit acctnum; Cards; 1 111 9 999 ; Run; I need to identify what are the records in COL file that are not found in COPS file. My approach: I have done merging like below with the condition highlighted in green. Proc sort data=cops out=t1;    by transit acctnum;    run;    proc sort data=col out=t2;    by transit acctnum;    run;    data t3 ;    merge t1 (in=a)           t2 (in=b)              ;              by Transit acctnum;              if b and not a;      run; This is the answer which is correct. 1 9 999 Question: I have tweaked the merging condition as shown in yellow. This still gives the correct answer. Could any merging expert confirm whether green and yellow conditions both essentially do the same. I am asking this because the answer may be “yes” for this small data set but when I apply to my larger dataset, SAS may do something else which I cannot detect. data t4 ;    merge t1 (in=a)           t2 (in=b)              ;              by Transit acctnum;              if not a ;      run; Thank you for your help Mirisage ... View more

Hi SAS Forum, This code was written by another programmer. I am trying to understand two symbols highlighted in yellow color in it. %if &income = 0 %then %do;        data wave_2;          set wave_1;          by transit_branch acctnumber;          if ~(first.acctnum and last.acctnum) then do;            if currbal ~= (acctbal * -1) then delete;          end;          run; %end; %else %do;     proc sort data=wave_1 out=wave_2 nodupkey;          by transit_branch acctnumber;          run; %end; Q: Could you please let me know if the way I understand it correct. if ~(first.acctnum and last.acctnum) then do; This means, if not (first.acctnum and last.acctnum) then do; if currbal ~= (acctbal * -1) then delete; This would mean: if currbal ne (acctbal * -1) then delete; Thanks Mirisage ... View more

Hi SAS Forum, Could you please help me to understand this code piece. DATA want;                                                               SET have;       IF PUT(BRANCH,$2.) NE ' ' THEN              BRANCH = INPUT(PUT(BRANCH,$2.),2.);   RUN;       I take the first part of the above IF PUT(BRANCH,$2.) NE ' ' The meaning of this is: “ A numeric variable named “Branch” is converted into a character variable with a length of $2. And if the value of the converted i"Branch" variable is not equal to missing” Question 1. Is my interpretation correct? Question 2. But $2. should be the initial state and not eventual state. How come the initial state of a numeric variable is $2. Question 3. What is the meaning of next segment which is:       Branch  = INPUT(PUT(Branch,$2.),2.);   Your help is appreciated. Thanks Mirisage ... View more