BookmarkSubscribeRSS Feed
tmcrouse
Calcite | Level 5

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.

9 REPLIES 9
Vince28_Statcan
Quartz | Level 8

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
Calcite | Level 5

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.

NAMETYPELENGTHVARNUMFORMATLFORMATDINFORMATINFORMLJUSTMEMTYPECOMPRESSCHARSET
Inpatient_Facility1821600 01DATACHARANSI
MBR_SYS_ID181110 111DATACHARANSI
Office_Clinic1821500 01DATACHARANSI
Outpatient_Facility1821700 01DATACHARANSI
PROV_TIN291190$90DATACHARANSI
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
JNash
Calcite | Level 5

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

Vince28_Statcan
Quartz | Level 8

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
Calcite | Level 5

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
Super User Tom
Super User

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
Super User

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

tmcrouse
Calcite | Level 5

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_tincathspciimppacsfu30denomfu182denomrestudydenomperfscts
1111
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 NEED
stratacathspciimppacsperfsctsfu30denomfu182denomrestudydenom
11141212213
22253211332
DATA After Query
stratacathspciimppacsperfsctsfu30denomfu182denomrestudydenom
11141212
22253211


tmcrouse
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1776 views
  • 0 likes
  • 5 in conversation