Problem with set up graph

[Gieorgie]

I have a problem with setting up two graphics, one is fine, as in the bottom,

while the other comes out so I don't know what it's caused by. The appendix in the second chart converts dates to some numerical values.

This is my code:

*/GRAPH #1*/
proc summary data=diff nway;
    class policy_vintage;
    format policy_vintage vintf.;
    var POLICY_TODAY POLICY_PRIOR;
    output out=newdiff sum=;
	
proc sql;
    create table diff1 as
        select today.policy_vintage as policy_vintage
             , POLICY_TODAY
             , POLICY_PRIOR
			 , POLICY_TODAY - POLICY_PRIOR as DIFFRENCE
        from newdiff today
        LEFT JOIN
             (select *
              from _work.POLICY_VINTAGE_WEEKLY
              where run_date < today()
              having run_date = max(run_date)
             ) prior
        ON today.policy_vintage = prior.policy_vintage
   ;
quit;

/*GRAPH #2 /
proc summary data=tablea_new nway;
    class policy_vintage;
    format policy_vintage vintf.;
    var AKTYWNE WYGASLE;
    output out=newtabe sum=;

proc sql;
    create table diff_policy as
		select
		today.policy_vintage 
             , today.WYGASLE as EXPIRED_TODAY
             , prior.WYGASLE as EXPIRED_PRIOR
			 , today.AKTYWNE as ACTIVE_TODAY
             , prior.AKTYWNE as ACTIVE_PRIOR
			 , today.WYGASLE - prior.WYGASLE as DIFF_EXP
			 , today.AKTYWNE - prior.AKTYWNE as DIFF_ACT
        from newtabe today
        LEFT JOIN
             (select *
              from _work.policy_weekly
              where run_date < today() 
              having run_date = max(run_date)
             ) prior
        ON today.policy_vintage = prior.policy_vintage
   ;
quit;

[PaigeMiller]

We don't have your data. Can you please share it with us, as SAS data step code (instructions)?

You didn't show any code for PROC SGPLOT. Can you please show us the entire code you are working with?

[Gieorgie]

Hi, here is data from log:

data WORK.DIFF1;
  infile datalines dsd truncover;
  input policy_vintage:VINTF. POLICY_TODAY:32. POLICY_PRIOR:32. DIFFRENCE:32.;
  format policy_vintage VINTF.;
datalines;
OLD 2932 2939 -7
2019-12 75 75 0
2020-01 60 60 0
2020-02 62 62 0
2020-03 72 72 0
2020-04 70 69 1
2020-05 85 85 0
2020-06 85 85 0
2020-07 108 109 -1
2020-08 101 101 0
2020-09 96 96 0
2020-10 108 109 -1
2020-11 118 118 0
2020-12 91 91 0
2021-01 76 77 -1
;;;;
proc sgplot data=diff1;
where policy_vintage gt &date_old.;
series x=policy_vintage y=POLICY_TODAY / markerattrs=(color=vligb symbol=circlefilled size=20) 
lineattrs=(color=vligb thickness=2);
series x=policy_vintage y=POLICY_PRIOR / y2axis markerattrs=(color=salmon symbol=circlefilled size=9) 
lineattrs=(color=salmon thickness=2);
run;

Second one :

data WORK.DIFF_POLICY_ACT;
  infile datalines dsd truncover;
  input policy_vintage:VINTF. ACTIVE_TODAY:32. ACTIVE_PRIOR:32. DIFF_ACT:32.;
  format policy_vintage VINTF.;
datalines;
2021-12 8928 8864 64
2022-01 9546 9553 -7
2022-02 8190 8193 -3
2022-03 9259 9269 -10
2022-04 9061 9066 -5
2022-05 8784 8790 -6
2022-06 9146 9151 -5
2022-07 9408 9412 -4
2022-08 8302 8304 -2
2022-09 8948 8942 6
2022-10 8773 8614 159
2022-11 902 743 159
2022-12 414 413 1
2023-01 401 402 -1
2023-02 384 384 0
;;;;
proc sgplot data=diff_policy_act;
where policy_vintage gt &gv_date_dly.;
series x=policy_vintage y=ACTIVE_TODAY / markerattrs=(color=vligb symbol=circlefilled size=2) 
lineattrs=(color=vligb thickness=2);
series x=policy_vintage y=ACTIVE_PRIOR / y2axis markerattrs=(color=salmon symbol=circlefilled size=9) 
lineattrs=(color=salmon thickness=2);
run;

[PaigeMiller]

Hello, thank you for providing the data in the requested format.

However, I still can't use your code as I don't have the VINTF. format and informat, and I don't have the value of your macro variable &gv_date_dly.

[Gieorgie]

Please see below code:

gv_date_dl - its came from promt

%let date_old=%sysfunc(intnx(year,%sysfunc(Today()),-2,s));
%put &=date_old;
proc format;
    value vintf low-&date_old = 'OLD' other=[yymmd7.];
run;
%let gv_date_dly=%sysevalf(%bquote('&date_dly.'d));

[PaigeMiller]

%let gv_date_dly=%sysevalf(%bquote('&date_dly.'d));

There is no &date_dly macro variable to use in your code. And you do NOT need to format macro variables, I have removed the unnecessary formatting of macro variables.

How about this:

%let date_old=%sysfunc(intnx(year,%sysfunc(Today()),-2,s));
%put &=date_old;
proc format;
    value vintf low-&date_old = 'OLD' other=[yymmd7.];
run;
%let gv_date_dly=&date_old;

data WORK.DIFF_POLICY_ACT;
  infile datalines truncover;
  input policy_vintage:anydtdte. ACTIVE_TODAY:32. ACTIVE_PRIOR:32. DIFF_ACT:32.;
  format policy_vintage yymmd7.;
datalines;
2021-12 8928 8864 64
2022-01 9546 9553 -7
2022-02 8190 8193 -3
2022-03 9259 9269 -10
2022-04 9061 9066 -5
2022-05 8784 8790 -6
2022-06 9146 9151 -5
2022-07 9408 9412 -4
2022-08 8302 8304 -2
2022-09 8948 8942 6
2022-10 8773 8614 159
2022-11 902 743 159
2022-12 414 413 1
2023-01 401 402 -1
2023-02 384 384 0
;

proc sgplot data=diff_policy_act;
where policy_vintage gt &gv_date_dly.;
series x=policy_vintage y=ACTIVE_TODAY / markerattrs=(color=vligb symbol=circlefilled size=2) 
lineattrs=(color=vligb thickness=2);
series x=policy_vintage y=ACTIVE_PRIOR / y2axis markerattrs=(color=salmon symbol=circlefilled size=9) 
lineattrs=(color=salmon thickness=2);
run;

This produces what looks like a reasonable graph. Is it what you want?

[Gieorgie]

Thanks for the reply & gv_date_dly .; is the promt in which we set today's reporting date.
while data_old is used to aggregate dates older than -2 years.

When i runed your code its nothing special happend. Still I see such a graph

[Gieorgie]

proc sql;
    create table diff_policy_act as
		select
		 akty.policy_vintage as date
			 ,akty.AKTYWNE as TODAY
			 ,prior.AKTYWNE as PRIOR
        from policy_vintage_weekly2 akty
		        LEFT JOIN
             (select *
              from _work.policy_weekly
              where run_date < today() 
			  and policy_vintage gt &gv_date_dly.
              having run_date = max(run_date)
             ) prior
        ON akty.policy_vintage = prior.policy_vintage
;
quit;
proc sgplot data=diff_policy_act;
series x=date y=TODAY / markerattrs=(color=vligb symbol=circlefilled size=2) 
          lineattrs=(color=vligb thickness=2);
series x=date y=PRIOR / y2axis markerattrs=(color=salmon symbol=circlefilled size=9) 
          lineattrs=(color=salmon thickness=2);
run;

I got like a below dataset:

data WORK.DIFF_POLICY_ACT;
  infile datalines dsd truncover;
  input date:YYMMD7. TODAY:32. PRIOR:32.;
  format date YYMMD7.;
datalines;
2021-11 6778 7091
2021-12 8940 8864
2022-01 9549 9553
2022-02 8195 8193
2022-03 9270 9269
2022-04 9070 9066
2022-05 8792 8790
2022-06 9161 9151
2022-07 9416 9412
2022-08 8316 8304
;;;;

My erro is same but i expect get some like a below:

[PaigeMiller]

My erro is same but i expect get some like a below:

I don't know what error you are referring to. Please explain in much more detail.

[Gieorgie]

Sorry i lost my mind. I mean still got this : Why i dont see any date and why line its look like this ? How to do to get the graphs as shown above

[BrunoMueller]

Your date values look really strange, compared to the data provided. 

I also assume you changed the x axis type to discrete so that the "old" values are all "together"

See this sample program below to illustrate what I mean

data timeseries;
  do year = 2018 to 2022;
    do month = 1 to 12 by 3;
      someDate = mdy(month,1,year);
      value = rand("integer", 50, 100);
      output;
    end;
  end;
  format
    someDate yymmd7.
    value comma15.
  ;
run;

proc format;
    value vintf (default=7)
      low - "31dec2020"d = 'OLD'
      other=[yymmd7.]
  ;
run;


proc sgplot data=timeseries;
  series x=someDate y=value;
run;
proc sgplot data=timeseries;
  series x=someDate y=value;
  format someDate vintf.;
run;
proc sgplot data=timeseries;
  series x=someDate y=value;
  format someDate vintf.;
  xaxis type=discrete;
run;

[PaigeMiller]

The code I provided produces this graph:

When you run the EXACT code I provided, do you get the result I am showing here?

[Reeza]

Run a proc freq on your policy_vintage and post that output please.

proc freq data=DIFF_POLICY_ACT;
table policy_vintage;
format policy_vintage;
run;

proc freq data=diff_policy_act;
table policy_vintage;
format policy_vintage vintf.;
run;

[Gieorgie]

Hi Reeza below first one screen :

proc freq data=DIFF_POLICY_ACT;
table policy_vintage;
format policy_vintage;
run;

This is for :

proc freq data=diff_policy_act;
table policy_vintage;
format policy_vintage vintf.;
run; 

[Reeza]

You need to show the full list.