topic Different counts in Excel and SAS in SAS Programming

Different counts in Excel and SAS

yiapipi — Wed, 19 Jun 2019 09:46:19 GMT

hi All,

running with 9.4 SAS i'm experiencing a trouble with gspanel "BAR CHART" rapresentation...

proc sgpanel data=rwork.DATI_FIN_sorted_2 (where=(MERCATO = ('CORPORATE') )) ;

format NDG comma18.0 ;

styleattrs datacolors=(medium_orange brown lime green );

title1 f='Albany AMT/bold' h=0.8 color=depk justify=center

"rilevazione documenti fiscali su perimetro BMRC del &BMRC_DT" ;

title2 f='Albany AMT/bold' h=0.8 color=depk justify=center

"eseguito alla data del &rundt" ;

panelby coverage / layout=columnlattice novarname noborder colheaderpos=bottom;

Vbar CLUSTER_ANNO / response=NDG group= TIPO_DOCUM groupdisplay=CLUSTER

/*clusterwidth=1*/ stat=freq dataskin=gloss BARWIDTH=1

datalabel datalabelattrs=(weight=bold) /*seglabel*/ seglabelattrs=(size=10) ;

colaxis LABEL='CLUSTER ANNO BILANCIO' ;

rowaxis LABEL= '# Bilanci' grid;

run;

quit;

substantially a DB of 12.357 NDG observation (Corporate) is showed in Histogram graph of 13.649 observation......

attacched you'll find data in sheet (FF) and , graph and log result in sheet (GRAPH_LOG).

thank's a lot for your support

KRegards

Pio Piccinin

Re: proc sgpanel

Rick_SAS — Mon, 17 Jun 2019 19:34:18 GMT

What is your question? I do not see any errors in the log and the picture looks like a bar chart clustered by a group variable.

Re: proc sgpanel

yiapipi — Tue, 18 Jun 2019 09:36:30 GMT

Hi Rick, thank's in advance for your prompt answer...

the item is related to the number reported in graph..

looking at "STOCK_Anni_preced" you'll find 589 for "DOC_FISC_SCADUTO" and 2557 for "BIL_SCADUTO".

referring to the input file, the right value should be 456 for "DOC_FISC_SCADUTO" and 2363 for "BIL_SCADUTO".

it seems a wrong count in computation...

thank's again

Pio

Re: proc sgpanel

Rick_SAS — Tue, 18 Jun 2019 12:58:05 GMT

I do not know why you think the correct answer should be something different, but here's how you can confirm the SGPANEL values:

proc freq data=rwork.DATI_FIN_sorted_2 (where=(MERCATO = ('CORPORATE') )) ;
format NDG comma18.0 ;
tables CLUSTER_ANNO * TIPO_DOCUM / norow nocol nopercent;
run;

The numbers in the PROC FREQ output should match the numbers in the SGPANEL output. I tried it on the data you provided and got the same numbers from PROC FREQ and PROC SGPANEL.

If these are not the numbers that you are expecting, then check the conversion of the Excel spreadsheet into a SAS data set. Perhaps some data were corrupted during the process.

Re: proc sgpanel

yiapipi — Tue, 18 Jun 2019 14:11:10 GMT

hi Rick, effectively is properly this the matter.

the selection i refer, is different among excel and SAS graph (as in previous post said) and i dont know a way to check the conversion of the Excel spreadsheet into a SAS data set.

the sum in excel data set of CORPORATE/STOCK_Anni_Preced are 2819 and not 3146....

the proc freq you rightly suggest is aligned on sas Graph nor to excel, this is clear and i hope you agree on this

may be other SAS User has experienced this?

thank's for your patient and precious support

Pio

Re: proc sgpanel

Rick_SAS — Tue, 18 Jun 2019 14:37:13 GMT

So it seems this is not a graph problem.

Why do you think the Excel spreadsheet has a different count? Are you using an Excel formula?

I cut and pasted the data into SAS. I get the same count as SAS. I think the data in the spreadsheet that you posted has the numbers that SAS reports.

Re: proc sgpanel

yiapipi — Tue, 18 Jun 2019 15:00:43 GMT

absolutely not Rick, no excel formula is used...

please review excel data i sent (not in SAS enviroment) and you'll see it's different from SAS report in each group representation

pherhaps proc gspanel plays in a different way data before to group them?

once again

ciao

Pio

Re: proc sgpanel

Rick_SAS — Tue, 18 Jun 2019 15:26:34 GMT

You still haven't told us why you think the Excel data is different. I do not see any difference.

Since this is not a graphing question, you might get more feedback if I move this question to a more general community. Would that be okay with you?

Good luck with your project.

Re: proc sgpanel

yiapipi — Wed, 19 Jun 2019 08:02:09 GMT

Ok Rick, move this topic on the group you think better..

thank's for your time and support

Pio

Re: proc sgpanel

Patrick — Wed, 19 Jun 2019 10:55:02 GMT

@yiapipi

Looking into the XLS you've posted it appears this is in an old Excel version with a limit of 65K rows in a spreadsheet.

I believe you simply don't have all the data in the Excel as compared to the SAS data set and that's why the results are different.

Re: Different counts in Excel and SAS

yiapipi — Wed, 19 Jun 2019 11:05:37 GMT

Thank's Patrick, you are right.

no other words to comment the oversight....

once again Thank's

Pio