09-12-2013 08:29 AM
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
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.
where office_clinic is not null
group by prov_tin
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.
09-12-2013 08:53 AM
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.
09-12-2013 09:16 AM
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.
09-12-2013 02:58 PM
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.
09-12-2013 03:00 PM
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?
09-12-2013 05:12 PM
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 After Query|
09-12-2013 05:24 PM
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.