Hi @Reeza,   It did work. Thanks a lot for the solution. ... View more

I want to print only the first output to excel but under different sheets and each sheet should have the name of the string I am inputting in macro.    %macro i_data (string,file); ods tagsets.ExcelXP file="designation/result.xls" options ( sheet name = &string. /*string name used in macro function*/ sheet_interval = 'None' ); proc print data = input.&file.;  run; ods tagsets.ExcelXP close;   proc print data=input.&file.; where column_1="0"; run;   %mend ; %i_data(ip1,file1); %i_data(ip2,file2); %i_data(ip3,file4); ... View more

Hi @Reeza,   Thanks for your reply. I have other proc print statements as well in my code. If that's the case, isn't the output of all print statements will be imported to excel?    I want only the output of a particular proc print statement to be saved in excel and the sheet name should be named after the string I enter in the macro function.  ... View more

Hi  @ChrisHemedinger   Can you please help me delete this post as I had framed the question wrongly.   Thanks ... View more

Hi @Tom ,   I have attached a .txt file that is somewhat similar to my file and the excel file has my desired output.    Thank you for your help.    ... View more

Hi @mkeintz,   Thanks for your reply.  The text after "includes.." and below "To be considered:" till a blank (as in the example above) should be the columns headers, and I want two columns for each. This column will be assigned values from the main data, which starts with a number, for example, 123456789123, and the fields under that number belong to it with variables name same as the column headers that we have mentioned above.  All the prior rows till the first "include" can be ignored.  Yes, I want only the rightmost two values, no matter how much content falls between the content identifier. And the 1st value will be under ABC_1 and the second ABC_2 (for example).  The data values are not numeric but have a fixed format; for example, view 00001 no #'123456789123.  The content identifiers are not always three letters long; they can vary.    ... View more

Hi @Tom ,   Sorry for the trouble. So, in this case, the code will look somewhat like this: data consider (keep=varname) results (keep=id row varname col value) ; infile txt truncover length=ll column=cc ; if _infile_ =: 'view' then do; input @ '#' id ; end; /*if not missing('view') then do; id = dummy; retain id; input / dummy ?? @@ ;*/ do row=1 by 1 while( missing('view')); input @1 varname :$32. @ ; do col=1 by 1 while(cc<= ll ); input value id @; if not missing(value) then output results; end; input @ '#' id @@; end; end; else if _infile_=:'Include' then do; input varname ; output consider; end; run; I am not sure if I have done it correctly. Please do correct me. Thanks and sorry again.  ... View more

Also, suppose if there is some string present in front of a number for example: view 00001 no #123456789123 then can we define dummy using a substring function to get the desired number that is 123456789123? ... View more

How to define the length and column. In your case it's 11 and column=cc.  ... View more

Thanks a lot for the reply.    Just a quick question: what does dummy ?? @ signify in the program.  ... View more

Hi @SASKiwi ,   Please find attached data for your ref.  ... View more

Hi @ballardw,   Thanks a lot for your reply.  Actually, I have attached my data, and it's a .txt file.    The Random data lines contain headings of the data and all the other text, which can be ignored.  The text after "includes.." and below "To be considered:" till a blank (as in the example above) should be the columns headers and I want 2 columns for each. I want this to assign values from the main data, which starts with a number, for example, 123456789123, and the fields under that number belong to it with variables same as the column headers we have mentioned above.    So, I want my output to look like this: Column1 ABC_1 ABC_2 DEF_1 DEF_2 GHI_1 GHI_2 JKL_1 JKL_2 XYZ_1 XYZ_2 STU_1 STU_2 LMN_1 LMN_2 123456789123 12 34 56 37     64 73             23455122467 67 43     12 46 87 56 45 78         5674512344 78 45 12 54             32 82 45 36   I hope I have answered all your questions. Please help.    @SASKiwi @PaigeMiller: For some reason, my previous post got deleted. This is an example of what my data looks like and what I want. Any help will be greatly appreciated.    ... View more