counting values using a loop and code instead of repeating a block of sql

[HB]

No joy in Mudville.  Same error.

I walked back and don't see any extra tabs or spaces or anything in the datalines.... 

[Astounding]

OK, definitely puzzling.

If you run a PROC FREQ on con2, what do you see as values?

proc freq data=have;

tables con2 / missing;

run;

[HB]

"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.

[Haikuo]

The two system options statements have to be the exact position to make this work. First one set to null, second set to 0.

Other than that, you got me there.

[HB]

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

[Haikuo]

Sorry about that, HB.

1. options missing=''; this one is at the beginning of the code;

2. options missing=0; this one is right before transpose.

After all these worked out, you probably want to reset options missing to whatever your origianl setings are.

So before you run the code,

proc options option=missing value;

run;

That will tell you the setting of your missing= option. And after you finish the job, reset it back, just in case.

Haikuo

[HB]

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.

[Astounding]

We can try to debug, but first see if this simple version does the trick.  Excel should be able to open this:

ods html body='path_to_file.xls';

proc tabulate data=have;

class entity con1-con3;

tables entity, (con1 con2 con3) * n=' ';

run;

If this isn't good enough (keeping in mind that the format can be tweaked), it would help to print out about 25 records from the data set have2.

[Haikuo]

yep, let's take it easy, one step at a time, run the code section by section, stop at the first sight of any errors and post it.

[HB]

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.

[Astounding]

HB,

So far, so good.  A major advantage of this approach is that it uses everything in the data.  So if any suspect values creep into the data, they will automatically be part of the output.

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.)

A minor addition is to add at the end of the TABLES statement:

/ rts=12;

It will take away some of the blank space in the "entity" column.

Which path seems best?  Reformat this table, vs. debug?

[HB]

"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.

[Astounding]

If you could get con1_n as a column heading, would you want to edit that to become con1-n ?

If so, I think you might as well edit what you have now (unless you have a ton of additional columns that you haven't told us about).  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.

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.  Some of that could be cleaned up by adding this up front:

title ' ';

options nodate nonumber; 

[HB]

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. 

[Astounding]

HB,

When it comes to the missing values, PROC TRANSPOSE is the culprit.  It shifts the valid values up, replacing missing values.

If you could print out about 50 rows from have4, there should be a way to get you what you need here (although from my personal perspective, it might have to wait until tomorrow).  When you post, please note whether the numbers seem correct to you and in the proper place.

Good luck.