topic Re: How do I fix this scatter plot? in New SAS User

How do I fix this scatter plot?

AbhilashS — Thu, 21 May 2020 16:49:18 GMT

Hello there !

I cannot seem to fix a broken scatter plot. For an unknown reason, the points on the graph are going up (instead of up and down).

This is the code I am running

proc sgplot data=cardata;
scatter x=year y=age_adjusted_rate;
run;

Any ideas why?

Re: How do I fix this scatter plot?

Reeza — Thu, 21 May 2020 17:06:23 GMT

Show your log.

Is there a format applied to the library?

What happens if you plot the following:

proc sgplot data=sashelp.class;
scatter x=height y=weight;
run;

@AbhilashS wrote:

Hello there !

I cannot seem to fix a broken scatter plot. For an unknown reason, the points on the graph are going up (instead of up and down).

This is the code I am running

proc sgplot data=cardata;
scatter x=year y=age_adjusted_rate;
run;

Any ideas why?

Re: How do I fix this scatter plot?

Rick_SAS — Thu, 21 May 2020 17:08:39 GMT

Perhaps your Y variable is probably character instead of numeric? That can happen if you (mis)read the data from an Excel spreadsheet.

To check, run

proc contents data=cardata;

run;

and look at the type of the Age_Adjusted_Rate variable.

Re: How do I fix this scatter plot?

AbhilashS — Thu, 21 May 2020 17:11:12 GMT

Hi Reeza

Here is the log from the code I ran

I ran the code you suggested and the plot worked. What does that tell you?

Re: How do I fix this scatter plot?

AbhilashS — Thu, 21 May 2020 17:18:20 GMT

Hi Rick

Yes you are right. It is a CHARACTER. Is there a way to change them to NUMERIC?

Re: How do I fix this scatter plot?

Kurt_Bremser — Thu, 21 May 2020 17:27:51 GMT

How did that data arrive in SAS (import from external file, read from foreign database, ...)?

Re: How do I fix this scatter plot?

AbhilashS — Thu, 21 May 2020 17:32:36 GMT

Hi Kurt
It is a CSV file downloaded from a public database.
Thank you for those articles. I am going through them now.

Re: How do I fix this scatter plot?

Kurt_Bremser — Thu, 21 May 2020 17:36:21 GMT

Then you should give us an example of the csv file. Either copy/paste a few lines into a window opened with the </> button (open the file with an editor, not with Excel!), or attach it to a post.

Re: How do I fix this scatter plot?

AbhilashS — Thu, 21 May 2020 17:43:51 GMT

FILENAME REFFILE '/folders/myfolders/sas_datasets/results.csv';

PROC IMPORT DATAFILE=REFFILE
	DBMS=CSV
	OUT=WORK.cardata
	replace;
RUN;

Thanks Kurt.

I have attached both the CSV file I am using and the code I ran.

Re: How do I fix this scatter plot?

Reeza — Thu, 21 May 2020 18:06:14 GMT

It tells me the issue is not with SAS but with your data, so that's when you know to check the data types and formats.

You can convert a character variable to a numeric as follows though you should really back up and ensure you read it in properly from the start.

data temp;
set have;

age_adjusted_rate_num = input(age_adjusted_rate, 12.1);

run;

proc sgplot data=temp;
scatter x=year y=age_adjusted_rate_num;
run;

Re: How do I fix this scatter plot?

Kurt_Bremser — Thu, 21 May 2020 19:24:29 GMT

Do this:

data cardata;
infile reffile dlm=',' dsd truncover;
input
  Sex :$15.
  Race :$15.
  State :$20.
  Ethnicity :$10.
  Age_Group :$15.
  First_Year
  Last_Year
  Cause_of_Death :$50.
  Year
  Deaths
  Population
  Crude Rate
  Age_Adjusted_Rate
;
run;

to read the numbers as numbers.

Re: How do I fix this scatter plot?

ballardw — Thu, 21 May 2020 19:43:14 GMT

Exactly the same as https://communities.sas.com/t5/Graphics-Programming/Change-Scatter-Plot-Axis-Values/m-p/648398#M19906

This appears with two different people posting such to be homework. Bring to the instructor's attention that the data file is poorly structured CSV for Proc Import to read. If all of the values are quoted then Proc Import will think the data is character: Example rows.

"Sex","Race","State","Ethnicity","Age Group","First Year","Last Year","Cause of Death","Year","Deaths","Population","Crude Rate","Age-Adjusted Rate"
"Both Sexes","All Races","United States","Both","All Ages","1999","2016","Unintentional MV Traffic, Occupant","1999","18326","279040238","6.57","6.55966083710955"
"Both Sexes","All Races","United States","Both","All Ages","1999","2016","Unintentional MV Traffic, Occupant","2000","18649","282171936","6.61","6.59229940420289"

Re: How do I fix this scatter plot?

AbhilashS — Fri, 22 May 2020 08:24:34 GMT

Hi Ballardw

Thank you for pointing this out. This is actually a self-guided statistics course with this data specifically being generated from the CDC website. There is no live instructor but I will try and email your points to the course creator. It has been a challenge as the tutorials do not anticipate problems like the one I have had so I am quite thankful to the help I am getting.

Re: How do I fix this scatter plot?

AbhilashS — Fri, 22 May 2020 08:55:03 GMT

Thank you Reeza and KurtB for your patience and help.

Eureka !

Re: How do I fix this scatter plot?

ballardw — Fri, 22 May 2020 14:10:46 GMT

@AbhilashS wrote:

Hi Ballardw

Thank you for pointing this out. This is actually a self-guided statistics course with this data specifically being generated from the CDC website. There is no live instructor but I will try and email your points to the course creator. It has been a challenge as the tutorials do not anticipate problems like the one I have had so I am quite thankful to the help I am getting.

CDC explains entirely too much. I've worked with a number of there systems over the years and as soon as they went away from fixed column data formats for text things fell apart. I have one project involving them where the data down load involves about 60 CSV files. The "program" they provided to to read the data starts with a macro to open all the CSV files and save them as XLSX files and then use Proc Import to read those. I started to write code to read the CSV and ran into the exact same issue of numeric values in quotes and it isn't always easy to tell which variables that contain all-numeral values are supposed to be numeric or are actually text ID codes.

Re: How do I fix this scatter plot?

kristinsainani — Fri, 22 May 2020 15:42:17 GMT

A simple trick to change this variable to numeric is to add 0 to the variable (and name it something new):

data NEW;

set OLD;

rate2=age_adjusted_rate+0;

run;

Re: How do I fix this scatter plot?

kristinsainani — Fri, 22 May 2020 15:41:24 GMT

A simple trick to change this to a numeric variable is to add zero:

data NEW;

set OLD;

rate2=age_adjusted_rate+0;

run;

Re: How do I fix this scatter plot?

Reeza — Fri, 22 May 2020 15:47:58 GMT

@kristinsainani wrote:

A simple trick to change this variable to numeric is to add 0 to the variable (and name it something new):

data NEW;

set OLD;

rate2=age_adjusted_rate+0;

run;

This does work, but it's not recommended. It will generate a warning in the log about implicit conversion, so you're now used to seeing the messages in your log which makes it easier to miss other issues or to have them suppressed because you have too many notes/warnings.