Sorry for the confusion.   HAVE WANT FA;TG FA   TG GX;AB;CD GX   AB   CD I want to convert the 'Have' items into another new table (Want), ignoring the ;.   That's why im using below code   data want; set have; array parsed_vars(6)$10 new_var1-new_var6; i=1; do while(scan(result, i, ";") ne ""); parsed_vars(i) =scan(result, i, ";"); i+1; end; run; proc print data=want; run;   However, the above code will produce more column as below   New_Var1 New_Var2 New_Var3 New_Var4 New_Var5 New_Var6 FA TG . . . . GX AB CD . . .   To compile the value into one field, I'm using transpose:   data final; set want; length fundname $5; array temp{6} new_var1 new_var2 new_var3 new_var4 new_var5 new_var6; do fund=1 to 6; fundname = temp{fund}; output; end; keep fundname; proc sort nodupkey; by fundname; run;     FUNDNAME FX TG GX AB CD   For the last part, I have to output the main dataset and still need to call fundname for the summary part at the footer. Here I have no idea how to call the variables.   DATA last; SET main_dataset; FILE "path\monthly..txt" DLM='|' lrecl=257; IF _N_ = 1 THEN PUT @1 'header|header|'; PUT @1 variable                  variable ; IF _N_=J THEN DO; PUT @1 'FUND TYPE|FUND DESCRIPTION'; END; run;     header1|header2| test1|test2 test3|test| Fund Type| FX| TG| GX| AB| ... View more

actually i manage to find the solution. I will be using below coding   data want; set test; array parsed_vars(6)$10 new_var1-new_var6; i=1; do while(scan(product, i, ";") ne ""); parsed_vars(i) =scan(product, i, ";"); i+1; end; run; proc print data=want; run; 　 data _null_; set want; length prodname $5; array temp{6} new_var1 new_var2 new_var3 new_var4 new_var5 new_var6; do prod=1 to 6; prodname = temp{prod}; output; end; keep prodname; proc sort nodupkey; by fundname; run;   Now, i already have unique prodname. The only unanswered solution is to call that variable and putting at the footer of my delimeted textfile. Any idea on that? or maybe anyone would like to comment the above syntax. Thanks. ... View more

Oh yes....i put it wrongly. Instead of YYMMDD10. I put DDMMYY10. And yes, it looks fine. I just need to explain to user to avoid confusion. Thanks for the reminder. Thanks again @Tom.   ... View more

TQ for your reply Patrick. Appreciate it. Yes, by using DATE9. format, it is readable using date format and not converted into funny thing. Maybe I need to advise my user that DDMMYY10. will not be a suitable solution if they converted the dlm file into excel manually. As for now, I will assume that is the best solution. Thanks again.   ... View more

Thanks for your reply Tom. You are correct because when the value is larger than 12, the position will be left justified. Any values lower or equal to 12 will be right justified. I already assign format DDMMYY10. to the date before output to textfile, but the problem still occurs. However, in dataset, the date field is already stored as date format, so Excel shouldnt confuse with the format.   Any other solution? ... View more

This is the sample code to generate the files   libname a 'C:\Testing Data\ROP'; data try; FORMAT DATE DDMMYY10.; input date DDMMYY10.; cards; 13032011 22112016 01042015 04072012 run; DATA _NULL_; SET TRY; FILE "C:\Testing Data\REPORT DATE..txt" DLM='|'; IF _N_ EQ 1 THEN PUT @1 'DATE|'; PUT DATE; RUN;   when i open up with Excel, those data that contains 0 in the front, will be right justified and the other data that have two digit will be left justified. This might be the problem when open up the report using Excel, however, is there any solution to synchronize all data to be right justified even if I used Excel to read it? Thanks. ... View more

Tq for the reply Mr Patrick.   The sample code as per below DATA _NULL_; SET A.TRY; FILE "C:\Testing Data\ACCOUNT&REPTDT..txt" DLM='|'; IF _N_ EQ 1 THEN PUT @1 'DATE|ACCTNO|NAME|'; put DATE ACCTNO NAME; run;   currently the date is in DDMMYY10. format.   And yes, to view the file, I open up with Excel. I never try use Notepad as I want it to be viewed in Excel (as per request from boss). ... View more

Dear PG, if i use that code,i'll get the latest date for each code my concern is for account = b..because after sort descending by date, the code=660 appear in between 2 code of 780.. if such cases, the date for 780 that i should extract is the old date..not the latest date different from account=a because the code=660 seems to appear at the bottom ... View more

Thanks for ur reply PGStats.. account : a (after sorting by date) code.    Date                                                                       Code.                     Date 780.      20/3/2004.         --------->.   Expected output.         780 (start date).     20/11/2004 780.      20/11/2004.                                                            660 (end date).      6/8/2009 660.      6/8/2009 After i sort nodupkey by descending date, the expected output is as above..i take the latest date for 780 code Account : b (after sorting by date) code.    Date                                                                       Code.                     Date 780.      20/3/2004.         --------->.   Expected output.         780 (start date).     20/3/2004 660.      20/11/2004.                                                            660 (end date).      10/10/2010 780.      6/8/2008 660.      10/10/2010 The end date, i still pull the most latest one..but for 780 code, i cannot get the latest date (20/11/2004) because there are 660 in between 2 code of 780..my user insist if the code goes by that sequence, i need to take the oldest date for code 780 only..code 660 remain unchanged, means that still take the latest one.. i Hope u get the better picture and help me in solving this...tq ... View more