Hi Gamotte,   It worked.   Thank you so much ... View more

Hi Gamotte,   It worked.   Thank you so much ... View more

Hi Gamotte. thanks for reply,   I will try your solution now.   My logic here is     here its like back anfd forth. 1 is back and 2 is forth we need to count only forth ,how many times its going to forth. each id: so when it is first time changing 1 to 2 means no of times 1 for 1 and 2 for 1 if it goes to back(1)1 that means no change but value is 1 if it goes again 1 to 2 that mean second times it is going to forth(2) so value 1 for 1 and  2 for 2 if it goes to back(1)1 that means no change but value is 1 if it goes again 1 to 2 that mean second times it is going to forth(2) so value 1 for 1 and 2 for 3 hope it make sense. Thank you ... View more

Re: Finding number of times changing i num column:   1 to 2 so chnage is 1 2 to 1 so chnage is no so i need 1 only 1 to 2 so change is 2 if again 1 to 2 comes change is 3 if again 2 to 1 then change is again 1   hope you understood   please find the id 2 also. may be it helps; 2 1 1 2 15 1 2 1 1 2 1 1 2 1 1 2 15 2 2 1 1 2 15 3   thank you. ... View more

num column:   1 to 2 so chnage is 1 2 to 1 so chnage is no so i need 1 only 1 to 2 so change is 2 if again 1 to 2 comes change is 3 if again 2 to 1 then change is again 1   hope you understood ... View more

Perfect.. Thank you Xia ... View more

I gave sample  data please check. still i am getting same reults: please verify it. dono how to put missing for other observations data have; input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4 sp_5; cards; 1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23 33 2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26 12 ; run; ... View more

It is almost right but Vol_1 vol2 vol_3 and other rev_ and profit_ variables has three types only but  sp_ has 4 variables   what i mean is  vol_3,rev_3 are repeating  in 4th observation when we have not equal type of variables.so to stop repeating and place "."  in repeating places . because we may have different types of variables(for eg:  vol_1-vol6,7, and so on). so can you please tell me how to achieve it? your previous code: with sp_4 extra column:  data test; input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4; cards; 1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23 2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26 ; run; proc transpose data=have out=temp; by Item Category Subcategory; run; data temp; set temp; key=upcase(scan(_name_,1,'_')); run; proc sql; select distinct catt('temp(where=(key="',key,'") rename=(_name_=',key,' col1=',key,'_Fig))') into : list separated by ' ' from temp; quit; data want; merge &list ; by Item Category Subcategory; drop key; run; ... View more

For same problem: Suppose if there is extra column Sp_4 with some values then how to write program? please help. ... View more

Did u try last updated solution? I sent updated code and it works for you ... View more

proc sort data=have; by pnr atc_code type date; run; data need; set have; length change $ 10.; by pnr atc_code; /*create new variable change and keep constant if it is topical for first observation*/ /*compare topical values with next values if it changes to other non bio or bio then switched*/ if first.pnr and atc_code in ( 'D05AX52','D05AX02') then change=""; else if not first.pnr and atc_code in('D05AX52','D05AX02','L01XC02','L04AA24','L04AB01','L04AB02','L04AB04','L04AB05','L04AB06','L04AC03','L04AC07','A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change='switched'; /*compare non bio values with next values if it changes to other non bio or bio then switched*/ if first.pnr and atc_code in ('L01XC02','L04AA24','L04AB01','L04AB02','L04AB04','L04AB05','L04AB06','L04AC03','L04AC07') then change=""; else if not first.pnr and atc_code not in( 'D05AX52','D05AX02') and atc_code in('L01XC02','L04AA24','L04AB01','L04AB02','L04AB04','L04AB05','L04AB06','L04AC03','L04AC07','A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change='switched'; /*compare bio with next values if it changes to other bio then switched*/ if first.pnr and atc_code in('A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change=''; else if not first.pnr and atc_code not in( 'D05AX52','D05AX02','L01XC02','L04AA24','L04AB01','L04AB02','L04AB04','L04AB05','L04AB06','L04AC03','L04AC07') and atc_code in('A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change='switched'; if atc_code=lag(atc_code) then change=""; run; ... View more

even if code changes with in the group, it works. Just try it once. hope it works. If it doesnt work, tell me . ... View more

data have; length TYPE $20.; input pnr ATC_CODE $ type $ EDATUM $ 10. ; date=input(edatum,mmddyy10.); format date mmddyy10.; cards; 478 L04AX03 Nonbiologic_systemic 2/10/2010 478 D05AX52 Psoriasis 2/23/2010 478 L04AX03 Nonbiologic_systemic 3/5/2010 478 L04AX03 Nonbiologic_systemic 5/18/2010 478 L04AX03 Nonbiologic_systemic 8/2/2010 478 L04AX03 Nonbiologic_systemic 11/3/2010 478 L04AX03 Nonbiologic_systemic 2/7/2011 478 L04AX03 Nonbiologic_systemic 8/16/2011 478 L04AX03 Nonbiologic_systemic 11/22/2011 603 D05AX02 Psoriasis 8/24/2005 603 D05AX02 Psoriasis 10/13/2005 603 D05AX02 Psoriasis 6/2/2006 603 D05AX02 Psoriasis 7/19/2006 603 D05AX02 Psoriasis 4/3/2007 603 D05AX02 Psoriasis 12/7/2007 603 D05AX02 Psoriasis 1/4/2008 603 D05AX02 Psoriasis 1/4/2008 603 D05AX02 Psoriasis 10/2/2008 603 D05AX52 Psoriasis 3/16/2009 603 D05AX52 Psoriasis 3/16/2009 603 D05AX52 Psoriasis 5/7/2009 603 D05AX52 Psoriasis 8/21/2009 603 D05AX52 Psoriasis 11/9/2009 603 D05AX52 Psoriasis 3/19/2010 603 D05AX52 Psoriasis 7/30/2010 603 D05AX52 Psoriasis 7/30/2010 603 D05AX52 Psoriasis 1/13/2011 603 D05AX52 Psoriasis 5/3/2011 603 D05AX52 Psoriasis 12/12/2011 1103 L04AX03 Nonbiologic_systemic 1/17/2006 1103 L04AX03 Nonbiologic_systemic 8/23/2006 1103 L04AX03 Nonbiologic_systemic 11/9/2006 1103 D05AX52 Psoriasis 4/19/2007 1103 L04AX03 Nonbiologic_systemic 4/19/2007 1103 D05AX52 Psoriasis 10/26/2007 1103 D05AX52 Psoriasis 4/1/2008 1103 L04AX03 Nonbiologic_systemic 10/20/2008 1103 D05AX52 Psoriasis 10/20/2008 1103 L04AX03 Nonbiologic_systemic 4/7/2009 1103 L04AX03 Nonbiologic_systemic 11/20/2009 1103 L04AX03 Nonbiologic_systemic 11/12/2010 1103 L04AX03 Nonbiologic_systemic 8/12/2011 1103 D05AX52 Psoriasis 10/22/2011 1103 D05AX52 Psoriasis 12/29/2011 ; run; /*sort the data to keep the date in increase order to know subject moved to other treatment or not*/ proc sort data=have; by pnr date; run; data need; set have; length change $ 10.; by pnr; /*create new variable change and keep constant if it is topical for first observation*/ /*compare topical values with next values if it changes to other non bio or bio then switched*/ if first.pnr and atc_code in ( 'D05AX52','D05AX02') then change=""; else if not first.pnr and atc_code not in('D05AX52','D05AX02','L01XC02','L04AA24','L04AB01','L04AB02','L04AB04','L04AB05','L04AB06','L04AC03','L04AC07','A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change='switched'; /*compare non bio values with next values if it changes to other non bio or bio then switched*/ if first.pnr and atc_code in ('L01XC02','L04AA24','L04AB01','L04AB02','L04AB04','L04AB05','L04AB06','L04AC03','L04AC07') then change=""; else if not first.pnr and atc_code not in( 'D05AX52','D05AX02') and atc_code in('L01XC02','L04AA24','L04AB01','L04AB02','L04AB04','L04AB05','L04AB06','L04AC03','L04AC07','A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change='switched'; /*compare bio with next values if it changes to other bio then switched*/ if first.pnr and atc_code in('A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change=''; else if not first.pnr and atc_code not in( 'D05AX52','D05AX02') and atc_code in('A11CC03','D05BB02','L01BA01','L01BB03' ,'L01XX05' ,'L04AA21' ,'L04AD01' ,'L04AX03') then change='switched'; if atc_code=lag(atc_code) then change=""; run;   Hope this may help you 🙂 ... View more