Help using Base SAS procedures

SAS summation will not work

Posts: 69

SAS summation will not work

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.

from cardiac.final11b

where office_clinic is not null

group by prov_tin

having prov_tin>'000000000');


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.

Super Contributor
Posts: 339

Re: SAS summation will not work

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.

Posts: 69

Re: SAS summation will not work

Posted in reply to Vince28_Statcan

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.

Inpatient_Facility1821600 01DATACHARANSI
Office_Clinic1821500 01DATACHARANSI
Outpatient_Facility1821700 01DATACHARANSI
outpt_pci_mpin_flg1820200 01DATACHARANSI
prov_tin_cabg186400 01DATACHARANSI
prov_tin_cath186100 01DATACHARANSI
prov_tin_ct186200 01DATACHARANSI
prov_tin_imppac187000 01DATACHARANSI
prov_tin_nstemi1810500 01DATACHARANSI
prov_tin_pci186300 01DATACHARANSI
prov_tin_pci182fu_denom1815700 01DATACHARANSI
prov_tin_pci_30fu_denom1812900 01DATACHARANSI
prov_tin_pci_restudy_denom1811200 01DATACHARANSI
prov_tin_perf187100 01DATACHARANSI
prov_tin_pet186600 01DATACHARANSI
prov_tin_revasc187200 01DATACHARANSI
prov_tin_secho186500 01DATACHARANSI
prov_tin_tee186700 01DATACHARANSI
prov_tin_tte186800 01DATACHARANSI
Posts: 1

Re: SAS summation will not work

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

Super Contributor
Posts: 339

Re: SAS summation will not work

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.

Posts: 69

Re: SAS summation will not work

Posted in reply to Vince28_Statcan

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.

Super User
Super User
Posts: 8,111

Re: SAS summation will not work

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?

Super User
Posts: 23,724

Re: SAS summation will not work

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

Posts: 69

Re: SAS summation will not work

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:

11111 11
111 1 11
111 1
1111 1
1111 1
111 1 1 1
22211 11 1
22211 1
22211 1 11
2221 111
2221 1 1
DATA After Query

Posts: 69

Re: SAS summation will not work

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.

Ask a Question
Discussion stats
  • 9 replies
  • 5 in conversation