Thank you very much for the helps. It works! ... View more

Hello Cynthia_sas, Thank you very much for your suggestion. I appreciate. I rewrite my code just like belows:   proc Report data=table1 NOWD; Column ("General Info" cumsum urlstring Reporting exam form n) ("Group" p1 p2 p3 p4 p5) ; define cumsum/ noprint; define urlstring/ computed noprint; define Reporting / left display "Reporting_Group" group; define exam / center display "Exam" ; define form / center display "Form" ; define n /center display "Flags"; define p1 /center display "P<=0" ; define p2 /center display "0<P<=0.4"; define p3 /center display "0.4<P<=0.9" ; define p4 /center display "0.9<P<=1.3" ; define p5 /center display "P>1.3"; compute reporting; count+1; if (mod(count,2)) then do; call define(_row_,"style","style=[background=CXECEDEC]"); end; endcomp;   compute urlstring/character length=200; urlstring=catt("&dir.\Passrate_report_&mon..xml#Items!A",cumsum); endcomp;   compute n; if n~="0" then do; call define("exam",'URL',urlstring); call define("exam",'style','style={textdecoration=underline color=blue}'); end; endcomp; run;   The funny thing is if I use noprint  on cumsum, my hyperlinks do not work, and if  I print cumsum, the hyperlinks work. But in most cases, I don't want cumsum shows on my report.  Can you give some suggestion about that?     ... View more

Hello Cynthia_SAS,   I am still struggling in this problem. I reshaped my code but it still didn't work. Here is part of my code.    proc Report data=table1 NOWD; Column ("General Info" cumsum Reporting exam form n) ("Group" p1 p2 p3 p4 p5) ; define cumsum/ noprint; define Reporting / left display "Reporting_Group" group; define exam / center display "Exam" ; define form / center display "Form" ; define n /center display "Flags"; define p1 /center display "P<=0" ; define p2 /center display "0<P<=0.4"; define p3 /center display "0.4<P<=0.9" ; define p4 /center display "0.9<P<=1.3" ; define p5 /center display "P>1.3"; compute reporting; count+1; if (mod(count,2)) then do; call define(_row_,"style","style=[background=CXECEDEC]"); end; endcomp;   compute n; if n~="0" then do; urlstring= "C:\temp\exam_report_2017..xml#questions!A||'cumsum'"; call define("exam",'URL',urlstring); call define("exam",'style','style={textdecoration=underline color=blue}'); end; endcomp; run;   cumsum is the cumulative number of n.   n= 2               cumsum=2        1                               3        0                               3        0                               3        4                               7        0                               7 ............................................ If I use urlsting="C:\temp\exam_report_2017..xml#questions!A12"", i.e. hardcoding the rows number A1 , A3, A8......, the link works. If I want to refer to specific rows , row number= cumsum, how do I code?  urlstring= "C:\temp\exam_report_2017..xml#questions!A||'cumsum'"  doesn't work.   Thanks a lot! ... View more

I test you code and it works. Thank you very much, Merry Christmas! ... View more

Thanks for the idea. However, my data not always begin with "I'. It depends on the exam. Different exam has a different suffix. I want a general code to apply all kinds of exams ... View more

Thansk for the reply. But the  case is not always right.  Those beginning with "I " are questions and not always begin with "I". Some with "I", some with "N", some with "M".     ... View more

Hello Tom, Thanks for the solution. However, I did not get the all data and variable names when I used the code you provide.  Here is my code: filename copy temp; data _null_; infile "&bdir.\&inf." ; file copy ; if _n_ = 1 then do until (found) ; input @; if upcase(_infile_) =: '"ITEM TYPE"' then found=1; else input; end; input; put _infile_; run;   proc import datafile=copy out=want dbms=csv replace; delimiter="|"; getnames=yes; datarow=2; guessingrows=32767; run;   The dataset want  missed the part of variables and data. Those variables and data with the names of var28 - var35 on the file I attached are very important.  When I check the copy.dat file, it is also truncated, missing those variables and data too.       ... View more

Thanks for all the replies. I need to make clarification here. I do not want these info which are always on the first 13-15 rows of each file: Export author: Wendy Smith Export date: 11/29/201717 07:46:44 Data Source name: "Department" Item Bank name: "Shoes" Query name: "Shoes 2017" Query type: Item Characteristics: Collection Query Item type: Style Visual query specific information: Selected test versions: Version name: "1.00" Shoes name: "S10316456MT_1" Modified: 01/06/2016 Version name: "1.00" Shoes name: "S10316456MT_2" Modified: 01/06/2016 Version name: "1.00" Shoes name: "S10316567MT_3" Modified: 01/12/201   I only care about the variable names and the following data: These are the variable names begin with "Item Type". Sometimes the variable names’ order change and each file not always has the same numbers of variables.   "Item Type"|"Item ID"|"AA"|" BB"|"CC"|"DD"|"EE"|"FF"|"GG"|"Date"|"Owner"|"Paper Type"|"HH"|"II"|"Rev. Order"|"TT"|Type"|"Source"|"Status"|"Translation Date"|"Translator"|"UDA 2 - 2016"|"UDA 3"|"UDA 3"|"Item Recorded"|"Response"|" Weight"|"Statistical History Name"|"Language"|" Start"|" End"|"Group"|" Bench"|"Level"|"N"|" Value"|"Measure"|"KK"   The data are the follows: "Shoestype"|"I33573"|"New Value"|"1"|"0"|"New Value"|"New Value"|"none"|"None"|"2015_10"|"New Value"|"New Value"|"default"|"default"|"New Value"|"scored"|"New Value"|"Approved"|"New Value"|"New Value"|" "|" "|"none"|"TRUE"|"C"|1.00|"S1 Shoes Sept 2015"|"ENU"|"3/1/2014"|"8/9/2014"|""|"TRUE"|"S1 - Shoes"|"369.000000"|"0.540000"|"0.400000"|"1.210000"   "Shoestype"|"I33681"|"New Value"|"1"|"0"|"New Value"|"New Value"|"none"|"None"|"2015_10"|"New Value"|"New Value"|"default"|"default"|"New Value"|"scored"|"New Value"|"Approved"|"New Value"|"New Value"|" "|" "|"none"|"TRUE"|"C"|1.00|"S1 Shoes Sept 2011"|"ENU"|"8/10/2009"|"8/31/2011"|""|"TRUE"|"S1 - Shoes"|"1039.000000"|"0.950000"|"0.210000"|"-1.740000"   "Shoestype"|"I33687"|"New Value"|"1"|"0"|"New Value"|"New Value"|"none"|"None"|"2015_10"|"New Value"|"New Value"|"default"|"default"|"New Value"|"scored"|"New Value"|"Approved"|"New Value"|"New Value"|" "|" "|"none"|"TRUE"|"B"|1.00|"S1 Shoes Sept 2015"|"ENU"|"3/1/2014"|"8/9/2014"|""|"TRUE"|"S1 - Shoes"|"360.000000"|"0.880000"|"0.200000"|"-0.770000" ... View more

Thanks for the suggestion. But the variables name changes and each file may not have the same numbers of variable. ... View more

many thanks! ... View more

Thank you very much Cynthia_sas! You are right. I did make some mistakes here because I usually use the hyperlink link to the different sheets with the form name. If I still use the code here   compute qn; urlstring= "#'"||strip(qn)||"'!A1"; call define(_col_,'URL',urlstring); call define(_col_,'style','style={textdecoration=underline color=blue}'); endcomp; run;   When I click the values of qn column which is the flag column in Summary excel sheet, it will go to the first row of the sheet named with qn value. That is the way I usually used before.   However this time I prefer to go to the corresponding row of the sheet named questions. That means hyperlinks link to the rows of specific sheet. Different the qn's value(1,3,3,4,2) correspond to different rows. If qn = 0 1 0 0 5 1 2 When I click 1, it will go the first row of question sheet. When I click 5, it will got to second row of question sheet. If I click 1, it will go to 7th row of question sheet, and so on.  ... View more