Hi Kyoo, Proc transpose would work fine for your data. But what if the data is like this... 111 1-Jan 1 111 1-Feb 3 111 1-Mar 4 222 1-Jan 1 222 1-Apr 3 Order2 will not be included in your output. For this u have to go with data step as given below, DATA HAVE; INPUT Acct mail_date $ Order; DATALINES; 111 1-Jan 1 111 1-Feb 3 111 1-Mar 4 222 1-Jan 1 222 1-Apr 3 ; RUN; *Selecting the maximum of order; PROC SQL NOPRINT; SELECT MAX(ORDER) INTO :M_ORD FROM HAVE ; QUIT; PROC SORT DATA=HAVE; BY ACCT; RUN; DATA WANT(DROP=ORD MAIL_DATE I); SET HAVE (RENAME=(ORDER=ORD)); ARRAY ORDER(&M_ORD) $  ; BY ACCT; RETAIN ORDER: ; IF FIRST.ACCT THEN DO I = 1 TO &M_ORD; ORDER(I)=" "; END; ORDER(ORD)= MAIL_DATE; IF LAST.ACCT; RUN; ... View more

If your data looks  like this '101 Jan2014 1 Cream' then you can get the desired by the following program, Data test; INFORMAT Month MONYY7.; FORMAT MONTH MONYY7.; Input customer_id Month  Month_num Product $ ; Datalines; 101 Jan2014 1 Cream 101 Dec2013 12 Powder 102 Feb2014 2 Cream 102 Mar2014 3 Paste 102 Apr2014 4 cloth 103 Sep2013 9 Cream 103 Jan2014 1 Paste 103 Nov2014 11 Cloth 104 Feb2014 2 Cream 104 Mar2014 3 Paste 104 May2014 5 Cloth 104 Apr2014 4 Brush 204 Dec2013 12 Paste 204 Jan2014 1 Cloth 204 Feb2014 2 Brush ; Run; Proc SQL ; Create Table WANT  as         select customer_id, Month, Month_num, Product from test where CUSTOMER_ID  in             (Select Distinct Customer_id From                  (Select A.* From test as A Inner join test as B ON A.customer_id=B.customer_id                      Where  INTCK('MONTH',A.MONTH,B.MONTH)=1) AS T              Group By Customer_id              Having Count(Customer_id) ge 2)       order by customer_id,Month; Quit; ... View more

Hi UrvishShah, Without knowing the  year how will you know the exact sequence of Dec/Jan/Feb... what if it is Dec2012/Jan2014/Feb2014 ??? Then it would not be consecutive 3 months right.. ... View more

Hi dishantn Pls add year along with the month something like this... 204 Dec2013 12 Paste 204 Jan2014 1 Cloth 204 Feb2014 2 Brush ... View more

If that's the case then as RW9 mentioned you should include the year to know the sequence of months atleast in monyy. format. My code will only work for jan to dec of a single year. ... View more

Hi... what exactly is your query??? Do you want to include cust_id 204 in output??? ... View more

Hi dishant , Run the program it gives the exact output you wanted. ... View more

Try this: PROC SQL ; CREATE TABLE WANT  AS select * from test where CUSTOMER_ID  in   (SELECT DISTINCT CUSTOMER_ID FROM     (SELECT A.* FROM test AS A INNER JOIN test AS B ON A.CUSTOMER_ID=B.CUSTOMER_ID      WHERE  A.month_num = B.MONTH_NUM+1  ) AS T    GROUP BY CUSTOMER_ID     HAVING COUNT(CUSTOMER_ID) GE 2); QUIT; ... View more

Hi, If you want to create a dataset this might help you- PROC transpose data=Have out=want ; VAR name DOB Disease Classification_status .... ; BY name; RUN; If you want to create a report with one subject per page try this-  I have used ODS statement to create a PDF report, ods pdf file="\Report.pdf"; proc print data=new noobs; by name notsorted; pageby name; run; ods pdf close; ... View more

Thank you guys for ur ideas... But i wanted a macro like simple program which accepts a input sentence and creates sub variables with small length like 50 bytes. Proc rank seems to be a excellent idea but the grouping needs to done in trial and error method based on the sub variable length to get the accurate results. So i think its not usefull for automating. Please go through my program below and through me some ideas to improve it. ***Input sentence***; %let sentence=We mostly use the default behavior of the DATA STEP to create working code. However, certain common tasks are made easier by overriding the default behavior. In the Pharmaceutical Industry one such common task is LOCF.; ***Counting the words***; %let nn=%sysfunc(countw("&sentence")); ***Macro for splitting the sentence into separate variable***; %macro word_split ( sub_var_name=txt, /*sub variable name*/      sub_var_len=50,  /*length of sub variable*/      No_sub_var=4);  /*No of sub variable to be created*/    data new;    length s $200  &sub_var_name.1-&sub_var_name.&No_sub_var $&sub_var_len;    Sentence="&sentence";    array a(*) $20 w1-w&nn;  /*Splitting the sentence into individual words*/    do i=  1 to &nn;     a(i)=scan("&sentence",i);    end;    do i=  1 to &nn;     retain s;     a1=s;     s=catx (" ",trim(a1),trim(a(i)));  /*Concatenating the words one by one and*/     nlen=length(s);      /*calculating its length*/      if nlen gt %sysevalf((&sub_var_len)-10) then do;       %macro split_var ();  /*Macro for creating sub variable with values*/        %do i= 1 %to &No_sub_var;         if (length(trim(&sub_var_name.&i)) le 1) then          do;           &sub_var_name.&i = s;           s=" ";           nlen=.;          end;        %end;       %mend;       %split_var ();      end;     if nlen ne . then &sub_var_name.&No_sub_var = s;    end;   keep Sentence &sub_var_name.1-&sub_var_name.&No_sub_var; run; %mend; ***end of Macro***; %word_split (sub_var_name=var,      sub_var_len=50,       No_sub_var=5) ... View more