No the code block generating the excel file would need to change in 2 places. One- the name of the file outputted.  First time through the loop it might be fred.xls, second time through jane.xls, third time through george.xls    K:\place_where_files_go\hb\<a variable that changes each time through the loop and is the entity>.xls"; Two the SQL woudl change. Where fieldname5="fred", Where fieldname5="jane", Where fieldname5="george" and so on I was thinking this might somehow work from a listing. names="fred,jane,george" ... View more

Okay.  Ksharp swoops in and steals the correct answer designation becasue I could make that code work and modify it to do what I wanted. Hai.kou gets a helpful for the code and remembering that I needed it as an output file and putting me on the trail of ODS. Astounding gets a helpful for extreme responsiveness, stick-to-it-ness, and nice-ness. I crafted a solution that worked and in the process found another solution that worked. Still haven't solved another problem but I think I will open another thread for that. Thanks much to all. ... View more

"For your first question, I have nothing to say, only OP can judge it." The OP only needs some sort of header name to serve as a name for a mail merge field.  32 characters is plenty to work with in this case.  No magic meaning in the names.  I can see where 32 might be a limit in some cases, but here it is okay. Regarding the hard coding,. the OP is also an extremely inexperienced SAS programmer and although he understands that variable and loops might be more elegant (and indeed asked for on in the thread title) he in many ways almost prefers hard coding as it helps him see what is going on. KSharp:  At the moment I am trying to make your programming work.  I can't follow it all, but I seem to be following it well enough t translate my actual data in.  It's going well.  I have hopes that with a combo of your stuff, Astounding's output stuff, and Hai.kou's excellent work I may make this work. Off to mush some more. ... View more

Jiminey crickets! Go home and go to your kid's band concert and your whole thread explodes.  Goodness. Okay.  I am going to mush on this. Astounding you have been astounding.  Thanks. I'm going to hand out some corrects and helpfuls after I mush and translate. ... View more

" If you could print out about 50 rows from have4, there should be a way to get you what you need here" Like I say, If I knew how to do that, I would.  Here's trying. Okay.  Reset. This code runs without errors:: title ' '; options nodate nonumber;  data have; input entity $ con1 $ con2 con3 $; datalines; a y 1 d a y 2 i a y 3 d a y 1 d a n 2 d a n 3 d b y 1 di b y 1 di b n 1 ii b n 3 i b n 3 . b n 3 . c y 2 . c n 2 . c y 3 di c n 3 . d y 1 di d y 2 ii d y 3 i d n 2 i d y 1 d d n 2 d d y 3 . d n 1 i ; ODS LISTING CLOSE; ods output  table=have1; proc tabulate data=have; class entity con1 con2 con3; table entity ,       con1 con2 con3; run; ods output close; ODS LISTING; proc sort data=have1 out=have2 (keep=entity con1-con3 n); by entity; run; data have3 (drop=con2 rename=con4=con2); set have2; *con4=put (con2,1.); length con4 $5; con4 = left(put(con2,5.)); run; data have4; set have3; array v con1-con3; array vid $8. id1-id3; do i=1 to dim(v); if not missing(v(i)) then vid(i)=catx('_',vname(v(i)),v(i)); end; id=coalescec(id1,id2,id3); run; ods html body="have4.htm"; proc print; run; options missing=""; proc transpose data=have4 out=want (drop=_:) ; by entity; var n; id id; run; proc print; run; However, the output it makes is wrong and clearly affected by the missing values.  The output is here: http://momarda.brinkster.net/have4.htm con1_y should be 4,2,2,5 not 4,2,1,4 and con1_n should be 2,4,2,3 not 2,2,,3.  Cleary for con3_d 5, , ,2 is close to 5,0,0,2 and for con3_d 1,1, ,3 is close to 1,1,0,3. Okay. ... View more

con1_n or con1_y is just peachy keen fine.  Hyphen is not required. con1n and con1y would be fine.  Not picky. They are just column names for merge fields. "unless you have a ton of additional columns that you haven't told us about"   Not a ton.  5 conditions total.  The remaining 2 you haven't seen are Y/N just like con1.  My test file is 30,000 records and 8 entities.  Current file they are using is 44 entities an a couple hundred thousand records.  Could move to 200 entities and south of half a million records at some point. Counts will probably never be higher than 4 digits regardless. The only thing I haven't mentioned is that if for Entity A there were 2 Y observations and 4 N observations, I'm pretty sure they will want to report         Count   Percent Y      2          .33 N      4          .67         6 but I was figuring on futzing with that later and getting at least something up now.  This is turning out to possibly be a lot easier than I thought it would be so if you think it isn't much to throw in a percent, a percent of total, or a total,  I'm all ears, otherwise ignore it.  "If con1_n would be acceptable with no need to edit, we can start to debug.  I'd suggest  printing 25 records from have2, and 25 records from have3 to kick off the debugging." If I knew how to do that we might be going somewhere.  I don't know why Haikou's code runs now when it didn't before. It works now.  I get: Obs   entity   con1_n   con1_y   con2_1   con2_2   con2_3   con3_d   con3_i   con3_di   con3_ii 1      a           2           4            2            2            2           5            1           0            0 2      b           2           2            3            0            1           0            1           2            1 3      c           0           1            0            0            1           0            0           1            0 4      d           3           4            3            3            1           2            3           1            1 which is an interesting combination of correct and wrong results.  The count for for d con1Y is definitely 5.  It clearly has to do with how the missing values are being treated (notice a is correct- the data are only wrong it's only wrong where there are records with missing values).  If I understood how to fix that I would. If this table was correct and could be exported to Excel, I think we'd be done.  Then I would just translate it back to my real stuff. "Are you running the tests interactively in a continuous session, or are you starting a new instance of SAS each time?  That might impact the table picked up a leftover title." I had something else going.  A quit and restart cleaned it up.  ... View more

"The intent of Hai.kuo's program is to change the column headings to be con1_n, con1_y, etc. and so condense the column headings into one row instead of two.  (You can't get a SAS variable name to be con1-n, but you can get con1_n.)" That is what I would "clean up" manually if it didn't happen programatically. That rts thing didn't do much and actually picked up some leftover title from another program. ... View more

That runs.  I get: The SAS System con1 con2 con3 n y 1 2 3 d di i ii entity 2 4 2 2 2 5 0 1 0 a b 2 2 3 0 1 0 2 1 1 c 0 1 0 0 1 0 1 0 0 d 3 4 3 3 1 2 1 3 1 That might be mail merged with some clean up. ... View more

3275 proc options option=missing value; 3276 run; SAS (r) Proprietary Software Release 9.1 TS1M3 Option Value Information For SAS Option MISSING Option Value: 0 Option Scope: DMS Process How option value set: Options Statement NOTE: PROCEDURE OPTIONS used (Total process time): real time 0.02 seconds cpu time 0.01 seconds I have no idea what you want me to do. ... View more

"what do you see as values?"                                   The SAS System                       09:40 Tuesday, March 27, 2012  39                                                                   The FREQ Procedure                                                                    Cumulative    Cumulative                                   con2    Frequency     Percent     Frequency      Percent                                   ---------------------------------------------------------------------------------------------                                      1           8       33.33             8        33.33                                      2           7       29.17            15        62.50                                      3           9       37.50            24       100.00 Which seems correct. ... View more

You have no idea how much of that sentence I don't understand.  System options statements?  Huh? ... View more

No joy in Mudville.  Same error. I walked back and don't see any extra tabs or spaces or anything in the datalines....  ... View more

Astounding wrote: If the overall form is good enough, we can look at using ODS to get Excel-ready output. That's an extremely intriguing prospect. ... View more

Still blows up on the PROC TRANSPOSE.  Same errors. ... View more