P_S_ Tracker

Re: help with data step. find consecutive days that a condition is met for the card.

P_S_ — Wed, 23 Sep 2020 15:42:54 GMT

This works. The dif function is pretty neat. Thank you ballardw. I appreciate your help.

Re: help with data step. find consecutive days that a condition is met for the card.

P_S_ — Wed, 23 Sep 2020 15:12:58 GMT

Hi ballardw;

I have fixed the data step code. it should run now.

Thank you for the idea. I ran your code and it seems to me that the counter for cons_days is simply incrementing for the card if the tran_amt is >= 150.
I need to increment it only if the condition is met (tran_amt >= 150 ) and it also need to be consecutive day. For eg. IF you look at row 19 of my "desired output" picture, the cons_day is reset to 1 because the date in row 19 is jan 20, 2015 and the previous date for this card is Jan 08, 2015. So it is not continuous (consecutive) day. So we would reset it to 1 rather than increment it to a 4. Hope this makes sense.

help with data step. find consecutive days that a condition is met for the card.

P_S_ — Wed, 23 Sep 2020 15:03:36 GMT

Hi,

I have a dataset below that shows transaction date and amount for the card_num. I need to find how many consecutive days did the card transact with amount of >= 150.

I need to create a variable(cons_days) that will track the 1st day the condition is met for the card and increment the counter if the condition is met for the card in the consecutive days. I have included a picture of the desired output.

I appreciate your help.

Thank you,

data transaction;
input @1 card_num 2.
@4 amount 3.
@8 tran_date MMDDYY10. ;
format tran_date date9.;
datalines;
11 100 01/05/2015
11 150 01/06/2015
11 200 01/07/2015
11 250 01/08/2015
11 250 01/20/2015
12 150 01/15/2015
12 300 02/25/2015
13 100 01/08/2015
14 400 01/02/2015
15 100 01/02/2015
15 150 01/03/2015
15 150 01/04/2015
15 150 02/01/2015
15 250 02/02/2015
;
run;

DESIRED OUTPUT:

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

P_S_ — Tue, 09 Feb 2016 18:01:19 GMT

This works. Thank you ballardw.

I also want to add the background color to the "Eastern States" - let's say a blue color. I am having difficultly with the syntax to add the background. Is there a way to add background color?

Re: PROC REPORT AND ODS EXCELXP STYLE OPTIONS

P_S_ — Mon, 08 Feb 2016 20:30:00 GMT

Thank you Ballardw for pointing this out.

I have now posted the code as an attachement. Hopefully that will work and people will be able to see the code.

PROC REPORT AND ODS EXCELXP STYLE OPTIONS

P_S_ — Mon, 08 Feb 2016 20:21:20 GMT

Hi,

I have a question about controlling style options with PROC REPORT and ODS EXCELXP tagsets.

I am using SAS 9.4 (M2) and I output my PROC REPORT results to EXCEL using EXCELXP tagsets.

Here is my output and I am having following issues:

REGION is a variable in the dataset sashelp.shoes. When I use INLINE formatting in this variable (superscript with 3), it does NOT show up as BOLD and RED in color. You can see all other variables are in red color with bold weight. I need this in BOLD and RED.
EAST COAST and WEST COAST are NOT the variables in the dataset. These are created in COLUMN statement. The color is red and it is BOLD which is exactly what I want. But I need to control the background and want yellow and green background.
Eastern States(superscript 1) and (Western States superscript 2) are also NOT the variables in the dataset. They are also created in the COLUMN statement. Here both have INLINE FORMATTING with superscript 1 and 2 respectively. Here I lose the red color as well as bold but I need those. I also need a background of different colors (say blue and black).

This is the output I would like to have:

Thank you for the help.

THE CODE IS IN THE ATTACHMENT (code.txt).

Re: PROC SGPLOT : How to use Custom Colors in Stacked Bar Chart

P_S_ — Fri, 20 Mar 2015 19:36:46 GMT

Hi DanH,

Yes I am still using 9.3. Can you please share an example of creating the ODS style with the custom color? That solution might work in my case. I want to give that a try.

And by the way I am outputting the plots to PDF destination.

Thanks for the help.

Re: PROC SGPLOT : How to use Custom Colors in Stacked Bar Chart

P_S_ — Fri, 20 Mar 2015 18:48:53 GMT

Hi DanH,

This is great example in this case. But the problem is I have many variables that I need to create stacked bar chart on and each of the variable has different values and are not consistent between variables.

This will create a problem for me as I will need to define colors for lots of values.

I have used GCHART before and have defined colors in pattern statement and the graph would automatically pick up the colors.

pattern1 value=s color=red ;

pattern2 value=s color=green ;

pattern3 value=l5 color=black;

and I was thinking something similar to that in SGPLOT. Is that an option?

PROC SGPLOT : How to use Custom Colors in Stacked Bar Chart

P_S_ — Fri, 20 Mar 2015 17:45:28 GMT

Hi All,

I need some help using custom colors in Vertical Bar Charts.

I am using SGPLOT / VBAR statement and version is SAS 9.3.

Here is the simple example of what I have. But I would like to use user defined colors (let's say blue green yellow black brown etc etc).

Is there a quick and simple way to do that? Thank you for all your help.

P.S.

data shoes;
set sashelp.shoes;
where region in ('Africa', 'Middle East', 'United States', 'Canada');
run;

proc sgplot data=shoes;
vbar region / response=sales group=product groupdisplay=stack stat=sum;
run;

Re: PROC SGPLOT - LEGEND AND CURVELABEL TOGETHER

P_S_ — Wed, 18 Mar 2015 22:16:01 GMT

Thanks Ballardw. It solves my problem.

PROC SGPLOT - LEGEND AND CURVELABEL TOGETHER

P_S_ — Wed, 18 Mar 2015 20:06:05 GMT

Hi All,

I have sas 9.3. I am using SGPLOT and I have set of series plot and the legend for the lines and it works fine.

However I also need to add the label to each lines using curvelabel option so that the eye movement is reduced.

The problem is when I use curvelabel option the legend disappears.

Is there a way that I could have both in my plot?

Thank you for your help.

P.S.

Here is the code and the output as an example.

/*THIS GIVES ME THE LEGEND*/

proc sgplot data=sashelp.stocks(where=(date >= "01jan2000"d and stock = "IBM")) ;;

title "Stock Trend : IBM";

series x=date y=close /legendlabel="CCCC";

series x=date y=low /legendlabel="LLLL";

series x=date y=high /legendlabel="HHHH";

run;

Now I add the curvelabel option. It works but the LEGEND DISAPPEARS

proc sgplot data=sashelp.stocks(where=(date >= "01jan2000"d and stock = "IBM")) ;;

title "Stock Trend : IBM";

series x=date y=close /curvelabel='Close' curvelabelloc=inside curvelabelpos=max legendlabel="CCCC";

series x=date y=low /curvelabel='Low' curvelabelloc=inside curvelabelpos=max legendlabel="LLLL";

series x=date y=high /curvelabel='High' curvelabelloc=inside curvelabelpos=max legendlabel="HHHH";

legend ;

run;

How to Preserve ODS Gridded Layout when using Proc Document

P_S_ — Fri, 13 Feb 2015 21:14:50 GMT

Hi All,

This is my first attempt to use Proc Document while still preserving the ODS Layout in my PDF Output Destination.

I am using SAS 9.3. For this example I am using sashelp.stocks dataset.

Here is what I am trying to achieve:

I want 3 plots in each page.

1st page will have 1 column and 3 plots.

2nd page has 2 columns: Column 1 has 2 plots & Column 2 has 1 plot.

I was able to achieve it with the code below using ODS Gridded Layout but I want to preserve this layout when I use PROC DOCUMENT Later on.

/*This codes gives me the layout I want*/
%macro plot_stocktrend(company);

ods graphics on /height=4in width=5in border=on;

proc sgplot data=sashelp.stocks(where=(date >= "01jan2000"d and stock = "&company.")) description= "Stock Trend : &company";;

title "Stock Trend : &company";

series x=date y=close;

series x=date y=low;

series x=date y=high;

run;

title;

%mend plot_stocktrend;

%macro plot_volume(company);

proc sgplot data=sashelp.stocks (where=(date >= "01jan2000"d

and date <= "01jan2001"d

and stock = "&company.")) description= "Stock Volume : &company";;

title "Stock Volume vs. Close : &company.";

vbar date /response=volume;

vline date /response=close y2axis;

run;

title;

%mend plot_volume;

title;

goptions reset=goptions device=sasprtc target=sasprtc ftext='Arial' ftitle='Arial/bold' ;

options orientation = portrait papersize = letter nodate nonumber;

ods _all_ close;

ods noproctitle;

ods pdf file ="~/gridded_layout.pdf" pdftoc=1 startpage=never;

ods layout gridded columns=1 rows=3 row_heights = (3in 3in 3in ) row_gutter=.5in;

ods region height = 3in ;

%plot_stocktrend(IBM);

ods region height = 3in;

%plot_stocktrend(Intel);

ods region height = 3in;

%plot_stocktrend(Microsoft);

ods layout end;

ods pdf startpage=now;

ods layout gridded columns=2 rows=3 row_heights = (3in 3in 3in ) row_gutter=.5in;

ods region height = 3in ;

%plot_volume(IBM);

ods region height = 3in;

%plot_volume(Intel);

ods region height = 3in;

%plot_volume(Microsoft);

ods layout end;

ods pdf close;

OUTPUT:


PAGE 1

This example has just 6 plots arranged in 2 pages. But in reality I have over 150 plots that I need to output to PDF.
Thus in my PDF, I also need a good bookmark so that it will be easy to navigate the plots.

To achieve that I want to use ODS Document Destination and Proc Document. But at the same time I want to preserve my original layout that I have shown above.

I have used the code below. This creates a good navigation panel. But the layout is not preserved. It outputs one plot in each page. I need to modify this code so that I can somehow use my ODS Gridded Layout statement.

If there is any other techniques to achieve this, I would be happy to use that as well.

Please advise.

ods document name=mydocument(write);

%plot_stocktrend(IBM);

%plot_stocktrend(Intel);

%plot_stocktrend(Microsoft);

%plot_volume(IBM);

%plot_volume(Intel);

%plot_volume(Microsoft);

ods document close;

proc document name=mydocument;

list / levels=all;

run;

proc document name=mydocument;

make trend,volume;

setlabel \trend#1 "Stock Trend";

setlabel \volume#1 "Stock Volume";

copy \Sgplot#1\SGPlot#1 to \trend#1;

copy \Sgplot#2\SGPlot#1 to \trend#1;

copy \Sgplot#3\SGPlot#1 to \trend#1;

copy \Sgplot#4\SGPlot#1 to \volume#1;

copy \Sgplot#5\SGPlot#1 to \volume#1;

copy \Sgplot#6\SGPlot#1 to \volume#1;

run;

quit;

title;

goptions reset=goptions device=sasprtc target=sasprtc ftext='Arial' ftitle='Arial/bold' ;

options orientation = portrait papersize = letter nodate nonumber;

ods _all_ close;

ods noproctitle;

ods pdf file ="~/gridded_layout_w_doc.pdf" pdftoc=1;

proc document name=mydocument;

replay \trend#1;

replay \volume#1;

run;

quit;

ods pdf close;

RESULTS:

Navigation Panel is exactly what I need.
But plots are one per page.

HELP NEEDED WITH SGPLOT & SGPANEL

P_S_ — Wed, 28 Jan 2015 23:41:54 GMT

Hi All,

I am plotting some descriptive statistics of a variable over time to visualize the variable coverage.

X axis is the month.It spans from 2004-01-01 to 2014-05-01 on a monthly basis.

I am using SGPLOT on sas 9.3 and here is the code I am using. (The data is also attached below).

proc sgplot data=percentiles;

where portfolio='AAA';

series x=date y=score_p5 / lineattrs=(color=CXAAA03E thickness=2 pattern=solid) curvelabel='P05' curvelabelloc=inside curvelabelpos=max;

series x=date y=score_p25 / lineattrs=(color=CXff750 thickness=2 pattern=solid) curvelabel='P25' curvelabelloc=inside curvelabelpos=max;

series x=date y=score_mean / lineattrs=(color=CX84351D thickness=2 pattern=solid) curvelabel='MEAN' curvelabelloc=inside curvelabelpos=max;

series x=date y=score_p50 / lineattrs=(color=CXD1836D thickness=2 pattern=solid) curvelabel='P50' curvelabelloc=inside curvelabelpos=max;

series x=date y=score_p75 / lineattrs=(color=CX5A1804 thickness=2 pattern=solid) curvelabel='P75' curvelabelloc=inside curvelabelpos=max;

series x=date y=score_p95 / lineattrs=(color=black thickness=2 pattern=solid) curvelabel='P95' curvelabelloc=inside curvelabelpos=max;

needle x=date y=score_miss_pct / y2axis lineattrs=(thickness=3 color=CX4D946E ) transparency=.70;

xaxis label="Month" grid values=("01Jan2004"d to "01Dec2015"d by 1) interval=month type=discrete fitpolicy=rotatethin;

yaxis label="SCORE";

y2axis grid label="Percent Missing";

title "VAR = Score; PORTFOLIO=AAA";

run;

I need help with the following:

1. The date on the x axis is at an angle. I want to rotate that vertically. In SAS GRAPH there was a rotate option. I need to find a way to do that in SGPLOT.

2. I want the title inside of the box.

3. In the data (attached below), there are two portfolio - AAA and BBB. This plot is only for AAA. I want to show the same plot but in a panel for both the portfolios. This will help me see it across different portfolio. I tried

SGPANEL but with not much success. I am hoping someone could help. I have not used GTL but would be open to that if there is a way to display this in a panel.

4.Eventually i want this to output on a PDF. In the PDF however, the image seems a little distorted. I have tried DPI but still it is not as good. Please share tips that has worked for you.

I would appreciate any help/suggestions, codes would be even better

Thank you,

Distribution of Two Samples (Population)

P_S_ — Thu, 13 Nov 2014 17:54:18 GMT

Hi Everyone,

I need a statistical test to show that the distribution of two samples are the same.

I was thinking I could probably test to see if both the distributions are normal or not but then it necessarily does not have to be a normal distribution and these could be any other distribution. So this might not work for my case.

Is there a way i could test that the distibutions are the same. Any code/examples will be very helpful.

Thank you,

PS.

Re: Data Step / Arrays help needed !!!

P_S_ — Fri, 25 Jul 2014 14:49:49 GMT

Thank you all for your great help, ideas and inputs. This is a great user community.

Re: Data Step / Arrays help needed !!!

P_S_ — Thu, 24 Jul 2014 20:07:11 GMT

Hi Jaap, This would be a processing using a SAS dataset. Thus a solution that uses data step components, arrays, macros etc would be an ideal solution. Thank you.

Re: Data Step / Arrays help needed !!!

P_S_ — Thu, 24 Jul 2014 18:39:35 GMT

Yes I have access to IML. but i have not used it before.

Re: Data Step / Arrays help needed !!!

P_S_ — Thu, 24 Jul 2014 18:38:09 GMT

Hi Jaap,

Yes we will need to increment the month. Like for account 100, in the intial dataset, it has month 1 - 6. The month 1 balance is 870. So we spread out this balance based on index values. Please note the vector has 5 values. So we will need to spread out month1 balance over 5 months (month 1 - month 5)

( month 1 balance = 174

month 2 balance = 261

month 3 balance = 87

month 4 balance = 217.5

and month 5 balance = 130.5 ..all of this is captured in variable bal_1)

now we need to do the same thing for month 2 balance which is 1740. Spread this balance over 5 months based on vector and capture in bal_2.

So for month 6 balance, which is 5220, we need to spread this out again over 5 months based on index. So that will be month 6, 7, 8, 9 and 10.

So, yes if needed the months has to be increased. For the prototype we can use, each month balance will be spread over 5 months (based on the vector).

Hope this makes sense.

Solution doesnt have to be array based. It could be using any other logic.

Thank you,

Data Step / Arrays help needed !!!

P_S_ — Thu, 24 Jul 2014 17:10:15 GMT

Hi,

I have an interesting problem that I need to solve and need some help with.

Let's say I have 2 account numbers (100 and 200) that spreads across several months. Account 100 goes from month 1 to 6 and account 200 goes from month 1 to 4. Each account has balance (bal) associated to it in a certain month. The dataset example is shown below.

data test;

input account $ months bal;

datalines;

100 1 870

100 2 1740

100 3 2610

100 4 3480

100 5 4350

100 6 5220

200 1 1000

200 2 1500

200 3 1700

200 4 1200

;

run;

account	months	bal
100	1	870
100	2	1740
100	3	2610
100	4	3480
100	5	4350
100	6	5220
200	1	1000
200	2	1500
200	3	1700
200	4	1200

Now for each account and for each balance, I need to spead the 'bal' amount based on the vector below.

VECTOR

0.2

0.3

0.1

0.25

0.15

Note that the vector sums up to 1 (100%).

Example: for account 100, the month 1 bal, which is 870 has to be spread out by applying the vector..so it will be 0.2*870 = 174, 0.3*870=261, 0.1*870 = 87, 0.25*870 = 217.5 and 0.15*870 = 130.5. Please note that the sum has to equal to the bal for that particular month when we break it down (174+261+87+217.5+130.5 = 870).

This has to be repeaded for each month and for each loan.

The final dataset will look some thing like this (this is only for account 100 but would also need to do the same for account 200 in the dataset).

account	ORIGINAL DATA		BALANCE SPREAD OUT ACCORDING THE THE VECTOR PARAMETER
100	months	bal	bal_1	bal_2	bal_3	bal_4	bal_5	bal_6	TOTAL
100	1	870	174						174
100	2	1740	261	348					609
100	3	2610	87	522	522				1131
100	4	3480	217.5	174	783	696			1870.5
100	5	4350	130.5	435	261	1044	870		2740.5
100	6	5220	0	261	652.5	348	1305	1044	3610.5
100	7		0	0	391.5	870	435	1566	3262.5
100	8		0	0	0	522	1087.5	522	2131.5
100	9		0	0	0	0	652.5	1305	1957.5
100	10		0	0	0	0	0	783	783

Notice that the total in 'TOTAL' column is equal to the total in 'bal' column (18270), which it should, since we are just spreading out the bal.

Any ideas/suggestions for solving this will be of great help. I appreciate all your inputs.

Thank you,

Re: help with sas code data step

P_S_ — Thu, 27 Feb 2014 05:15:58 GMT

Here is the link. https://communities.sas.com/thread/53243