SAS summation will not work

[tmcrouse]

I  am really confused as to why this summation will not sum all the columns. There is 1 datatype yet it sums. Here is my code:

proc sql; *tin posofc;

create table cardiac.pos_tinofc as

(select

prov_tin as Strata,

sum(prov_tin_cath) as ofc_caths,

sum(prov_tin_pci) as ofc_pcis,

sum(prov_tin_imppac) as ofc_imppacs,

sum(prov_tin_secho) as ofc_secho,

sum(prov_tin_pet) as ofc_pet,

sum(prov_tin_tte) as ofc_tte,

sum(prov_tin_tee) as ofc_tee,

sum(prov_tin_revasc) as ofc_revascs,

sum(prov_tin_perf) as ofc_perfs,

sum(prov_tin_ct) as ofc_cts,

sum(prov_tin_cabg) as ofc_cabgs,

sum(outpt_pci_mpin_flg) as ofc_outptpci,

sum(prov_tin_pci_restudy_denom) as ofc_restudy_denom format=8.,

sum(prov_tin_pci_30fu_denom) as ofc_FU30_denom format=8.,

sum(prov_tin_pci182fu_denom) as ofc_FU182_denom format=8.

from cardiac.final11b

where office_clinic is not null

group by prov_tin

having prov_tin>'000000000');

quit;

I have tried it without putting the format=8. in because all columns are a datatype number of 8 with the exception of outptpci. The columns that are not working in the summation are the last 3 that have denom. If I run 2 separate proc sqls and put the 3 columns with denom in their own, it works just fine. I even tried putting the outptpci because it is text last and all others that are number above and it still will not sum the denoms, yet it will everything else including the outptpci. I dont get it. Any advice? Otherwise I guess it is 2 proc sqls and a merge on the 2.

[Vince28_Statcan]

Could you provide the format and datatype of your last 4 columns as well as, preferably, the error code?

changing a format via format= does not change their data representation and thus has no impact on the sum calculation itself. It only changes the displayed output.

If outptpci is character (e.g. "1" and "0" instead of 1 and 0), you would need to do sum(input(outpt_pci_mpin_flg, best32.)) as ofc_outptpci

I don't think the sum function supports natural char to num conversion so I don't see how your query could work with all variables up to and including outptpci based on what you've said about your data structure.

[tmcrouse]

I tried that and it errored with:

ERROR: INPUT function requires a character argument.

ERROR: The SUM summary function requires a numeric argument.

Here is what it says when I do a proc contents, I see now that outpt_pci_mpin_flg is a number, so that is why it is summing correctly. The denoms do not sum. The code does run, however the denom columns will not sum. Very odd.

NAME	TYPE	LENGTH	VARNUM	FORMATL	FORMATD	INFORMAT	INFORML	JUST	MEMTYPE	COMPRESS	CHARSET
Inpatient_Facility	1	8	216	0	0		0	1	DATA	CHAR	ANSI
MBR_SYS_ID	1	8	1	11	0		11	1	DATA	CHAR	ANSI
Office_Clinic	1	8	215	0	0		0	1	DATA	CHAR	ANSI
Outpatient_Facility	1	8	217	0	0		0	1	DATA	CHAR	ANSI
PROV_TIN	2	9	11	9	0	$	9	0	DATA	CHAR	ANSI
outpt_pci_mpin_flg	1	8	202	0	0		0	1	DATA	CHAR	ANSI
prov_tin_cabg	1	8	64	0	0		0	1	DATA	CHAR	ANSI
prov_tin_cath	1	8	61	0	0		0	1	DATA	CHAR	ANSI
prov_tin_ct	1	8	62	0	0		0	1	DATA	CHAR	ANSI
prov_tin_imppac	1	8	70	0	0		0	1	DATA	CHAR	ANSI
prov_tin_nstemi	1	8	105	0	0		0	1	DATA	CHAR	ANSI
prov_tin_pci	1	8	63	0	0		0	1	DATA	CHAR	ANSI
prov_tin_pci182fu_denom	1	8	157	0	0		0	1	DATA	CHAR	ANSI
prov_tin_pci_30fu_denom	1	8	129	0	0		0	1	DATA	CHAR	ANSI
prov_tin_pci_restudy_denom	1	8	112	0	0		0	1	DATA	CHAR	ANSI
prov_tin_perf	1	8	71	0	0		0	1	DATA	CHAR	ANSI
prov_tin_pet	1	8	66	0	0		0	1	DATA	CHAR	ANSI
prov_tin_revasc	1	8	72	0	0		0	1	DATA	CHAR	ANSI
prov_tin_secho	1	8	65	0	0		0	1	DATA	CHAR	ANSI
prov_tin_tee	1	8	67	0	0		0	1	DATA	CHAR	ANSI
prov_tin_tte	1	8	68	0	0		0	1	DATA	CHAR	ANSI

[JNash]

having prov_tin>'000000000')-does this look right?

[Vince28_Statcan]

GT/LT does work with strings as well and whilst it can occasionally provide awkward results, due to the characters binary representation being ordered, it may very well work for him. It would only produce odd cases if values of prov_tin had either fewer than 9 digits (with leading blanks or other characters than 0-9)

"01" is GT "00" but " 1" would be lower (comparison is char by char byte representation and blank space is lower than 0). Still, it wouldn't actually generate an error in the summation. It would only impact the results.

If you were talking about the closing paranthesis, it matches the one starting before the select statement and should not be causing any issue either.

From the data structure, I really don't see anything wrong.

Quick question, is cardiac.final11b a view or a table? I don't think it should matter I just recall reading some limitations of views a while back and thought if it's a view and the denom variables are viewed from a link to a different table it would give me something to read about.

[tmcrouse]

cardiac.final11b is a table. It is just those denom columns that are not summing. Trying to move them higher in the query to see what that does.

[Tom]

Double check for typo in dataset (or library) name.

Since only the three denom variables are requesting to have formats attached you could remove the format= options.  If the reason you wanted to attach the format is that the values are non-integers you might want to use the round() or int() function to convert the sum to an integer.

Also try changing the order of the variables in the query.  Perhaps there is some interaction with the line length on you files?

[Reeza]

Post your original error from SAS and verify your null condition works the way you expect it to.

[tmcrouse]

There is no SAS error. The query processes just fine. The problem is this: (please note, shorted the real column names for sake of space and did not include all the columns, just using this as example of data have and data need:

DATA HAVE
prov_tin	caths	pci	imppacs	fu30denom	fu182denom	restudydenom	perfs	cts
111	1
111	1	1			1	1
111			1				1	1
111				1
111	1					1
111	1		1
111				1		1		1
222	1	1			1	1		1
222	1	1		1
222	1	1		1		1	1
222	1		1	1	1
222	1		1		1
DATA NEED
strata	caths	pci	imppacs	perfs	cts	fu30denom	fu182denom	restudydenom
111	4	1	2	1	2	2	1	3
222	5	3	2	1	1	3	3	2
DATA After Query
strata	caths	pci	imppacs	perfs	cts	fu30denom	fu182denom	restudydenom
111	4	1	2	1	2
222	5	3	2	1	1

[tmcrouse]

I just figured it out. It is working for the ip and op because I have denom counts in it but not what I should. The reson is, I cannot do a summation of my denoms by ip, op and ofc because it does not include all the denoms for every patient. Not every patient has a place of service listed. So, the place of service is blank for some, therefore I miss if they have a count in the denom column. This is why my summation when QCing is not adding up. Thanks to all of you offering suggestions.