Line graph problem with xlabel

[Gieorgie]

I have data that was doing a line chart for me before adding an aggregate. After adding an aggregate, the chart broke. This is what the data looks like

date	sold	sold-1	diff
Expired date	2937	2939	-2
2021.01	75	75	0
2021.02	60	60	0
2021.03	62	62	0
2021.04	72	72	0
2021.05	69	69	0

My code for graph and for create above table:

	
proc sql;
    create table diff1 as
        select today.date
             , sold
             , sold-1
			 , sold- sold-1 as diff
        from newdiff today
        LEFT JOIN
             (select *
              from _product.hisotry
              where run < today()
              having run = max(run)
             ) prior
        ON today.date= prior.date
   ;
quit;
proc sgplot data=diff1;
series x=date y=sold / markerattrs=(color=vligb symbol=circlefilled size=20) 
          lineattrs=(color=vligb thickness=2);
series x=date1 y=sold1 / y2axis markerattrs=(color=salmon symbol=circlefilled size=9)  
          lineattrs=(color=salmon thickness=2);
run;

How do I remove the expired_date from the chart and make it show the dates every month?

[ballardw]

You can always exclude values with a WHERE statement or dataset option.

proc sgplot data=diff1;
where date ne "Expired date"; /* or whatever actual value is in the data, questionable between what you show as "data" and the axis labels*/
series x=date y=sold / markerattrs=(color=vligb symbol=circlefilled size=20) 
          lineattrs=(color=vligb thickness=2);
series x=date1 y=sold1 / y2axis markerattrs=(color=salmon symbol=circlefilled size=9)  
          lineattrs=(color=salmon thickness=2);
run;

IF your "date" variable is actually a SAS date value that you created a custom format for that displays "expired_date" then you would use an appropriate range to exclude.

If your date variable is character you may get what you want by using an XAXIS statement to set the type as discrete if the exclusion doesn't completely solve the axis appearance. However if the date is character there is no "month", just random strings and order may be questionable.

I suspect more than once someone has suggested that data is best as a data step so we don't have to guess at the properties of variables. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Note that without the source data in Newdiff and Today data sets the Proc SQL doesn't really add anything to the information for this post. Data of the created Diff1 data set would.

[Gieorgie]

Thanks for you respond, but when im trying use a where clause i got here 28 proc sgplot data=diff1;
29 where date ne "expired_date";
"ERROR: WHERE clause operator requires compatible variables. "

[ballardw]

---

@Gieorgie wrote:
Thanks for you respond, but when im trying use a where clause i got here 28 proc sgplot data=diff1;
29 where date ne "expired_date";
"ERROR: WHERE clause operator requires compatible variables. "

---

Which is exactly why I mentioned data in the form of a data step and brought up that your example "data" showed "Expired date" but the Axis tick marks show "expired_date". That means one of your "data" or graph is incorrect about the value of the variable and there is nothing that shows what type of variable your date variable is.

Also note that my first response includes:

IF your "date" variable is actually a SAS date value that you created a custom 
format for that displays "expired_date" then you would use an appropriate range to exclude.

You must know what "dates" are expired. Use a range of values like

Where   date ge '01JAN2018'd;

or similar to only have date values from Jan 1 2018 to present.

[Gieorgie]

Maybe because the proc format I have old and other and moving do date format like a below ?

proc format;
    value vintf low-&date_old = 'old' other=[yymmd7.];
run;
proc summary data=diff nway;
    class policy;
    format policy vintf.;
    var date_today date-1;
    output out=newdiff sum=;

expired_date is not a date other has date format

[ballardw]

---

@Gieorgie wrote:

Maybe because the proc format I have old and other and moving do date format like a below ?

proc format;
    value vintf low-&date_old = 'old' other=[yymmd7.];
run;
proc summary data=diff nway;
    class policy;
    format policy vintf.;
    var date_today date-1;
    output out=newdiff sum=;

expired_date is not a date other has date format

---

It does not matter what the format is for a variable when you use a WHERE or similar statement that does comparisons unless you explicitly use Put(somevar, someformatname.) in the comparison.

And I really don't get why a POLICY would use a DATE format to begin with. But the values to exclude use the same rules.

You still haven't shown a data step of any of the data involved.