topic Re: Problem with set up graph in SAS Programming

Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 09:24:29 GMT

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;

Re: Problem with set up graph

PaigeMiller — Mon, 08 Nov 2021 10:45:10 GMT

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?

Re: Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 11:08:31 GMT

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;

Re: Problem with set up graph

PaigeMiller — Mon, 08 Nov 2021 11:21:02 GMT

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.

Re: Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 11:27:16 GMT

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));

Re: Problem with set up graph

PaigeMiller — Mon, 08 Nov 2021 11:38:31 GMT

%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?

Re: Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 12:45:12 GMT

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

Re: Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 14:47:26 GMT

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:

Re: Problem with set up graph

PaigeMiller — Mon, 08 Nov 2021 14:58:29 GMT

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.

Re: Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 15:01:12 GMT

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

Re: Problem with set up graph

BrunoMueller — Mon, 08 Nov 2021 15:27:33 GMT

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;

Re: Problem with set up graph

PaigeMiller — Mon, 08 Nov 2021 15:43:13 GMT

The code I provided produces this graph:

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

Re: Problem with set up graph

Reeza — Mon, 08 Nov 2021 16:22:01 GMT

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;

Re: Problem with set up graph

ballardw — Mon, 08 Nov 2021 16:32:34 GMT

Just what does a "Policy_vintage" value of 3328-12 mean? Typically, and from the axis of the first graph, that might mean year 3328 with a months of December. So, what policy do you have that a date in 3328????

I suspect that somewhere along the line you have some values that are not dates being treated as such or possibly you are aggregating dates (sum or similar) so that you create values that are way of your expected range.

Re: Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 17:21:05 GMT

I have no idea why it shows this date and that's the problem. As on another chart, I showed that it creates ok

Re: Problem with set up graph

PaigeMiller — Mon, 08 Nov 2021 17:47:37 GMT

I have no idea why it shows this date and that's the problem.

Do you actually LOOK in the data set named DIFF_POLICY_ACT with your own eyes and see what is in there? Can you show us this data set (screen capture is fine)? You can do some debugging yourself.

Did you try the suggestion by @Reeza to run PROC FREQ on this data set both with and without the formats? What happened?

Please don't ignore the suggestions people make for debugging this.

Re: Problem with set up graph

Gieorgie — Mon, 08 Nov 2021 18:15:55 GMT

I don't ignore, however I had to leave my computer and I will come back to that tomorrow with any suggestions. I am very grateful for your help

Re: Problem with set up graph

Gieorgie — Tue, 09 Nov 2021 07:26:23 GMT

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;

Re: Problem with set up graph

Reeza — Tue, 09 Nov 2021 17:07:17 GMT

You need to show the full list.