About Mark7

Mark7 · ‎04-21-2023

Works great, thank you

Mark7 · ‎04-20-2023

Thank you! It works! i had to add %to %do i =1 %to 20;

Mark7 · ‎04-20-2023

Hi, I have over 400 tables named similair E_PROD_2002_01, E_PROD_2002_02, .... E_PROD_2002_20, etc. I need to alter a column in every table, to change length char from 10 to 15. I don't want to run every single code: proc sql; alter table dds.E_PROD_2002_01 modify name char(15); quit; Is there an easier way?

Mark7 · ‎03-03-2020

Thank you TomKari. I will start digging code from your blog.

Mark7 · ‎03-03-2020

Below original sas code. Without 2001/03 and 2001/04 works proper /* Set the graphics environment */ goptions reset=all cback=white border htitle=12pt htext=10pt; data ds1; input year $ exp rev; datalines; 1997/98 302.3 310.8 1998/99 323.0 328.7 1999/00 354.4 363.5 2000/01 381.7 390.2 2001/02 426.8 429.1 2001/03 481.7 490.2 2001/04 498.8 498.1 ; run; data ds2; set ds1; n=_n_; run; data _null_; set ds2 end=eof; if eof then call symput('skip',left(n)); run; data ds2; set ds2; resp=exp; mid='exp'; output; resp=rev; mid='rev'; output; run; proc sort; by year mid; run; data anno; set ds2; by year mid; length function color $8 text $20 style $ 20; /* Populate the table */ if first.year then do; function='move'; xsys='2'; ysys='1'; midpoint=mid; group=year; y=0; output; function='cntl2txt'; output; function='label'; xsys='A'; ysys='3'; x=+4; y=13; text=trim(left(put(exp,8.1))); color='black'; position='+'; when='a'; output; function='move'; xsys='2'; ysys='1'; midpoint=mid; group=year; y=0; output; function='cntl2txt'; output; function='label'; xsys='A'; ysys='3'; x=+4; y=9; text=trim(left(put(rev,8.1))); color='black'; position='+'; when='a'; output; end; /* Generate the table frame */ function='move'; xsys='3'; ysys='3'; x=3; y=6; output; function='bar'; xsys='1'; ysys='3'; x=100; y=15; style='empty'; color='black'; line=0; output; /* Generate the row headers */ function='label'; xsys='3'; ysys='3'; style='marker'; text='U'; color='cx7c95ca'; x=4; y=13; position='6'; output; function='label'; xsys='3'; ysys='3'; style='"Albany AMT"'; text='Expenditure'; color='black'; x=7; y=13; position='6'; output; function='label'; xsys='3'; ysys='3'; style='marker'; text='U'; color='cxde7e6f'; x=4; y=8.5; position='6'; output; function='label'; xsys='3'; ysys='3'; style='"Albany AMT"'; text='Revenue'; color='black'; x=7; y=9; position='6'; output; /* Generate the vertical lines in the table */ function='move'; xsys='1'; ysys='1'; x=0; y=0; output; function='draw'; xsys='1'; ysys='3'; x=0; y=6; line=1; color='black'; output; function='move'; xsys='1'; ysys='1'; x=100; y=0; output; function='draw'; xsys='1'; ysys='3'; x=100; y=6; line=1; color='black'; output; if first.year and n ^=&skip then do; function='move'; xsys='2'; ysys='1'; midpoint=mid; y=0; group=year; output; function='move'; xsys='A'; ysys='1'; x=+15.5; x=+12; y=0; output; function='draw'; xsys='A'; ysys='3'; x=+0; y=6; color='black'; line=1; output; end; /* Generate the horizontal line in the table */ function='move'; xsys='3'; ysys='3'; x=3; y=10.5; output; function='draw'; xsys='1'; ysys='3'; x=100; y=10.5; line=1; color='black'; output; run; title1 'Revenue and Expenditures, 1997/98-2001-02'; axis1 label=(a=90 'Millions of Dollars') order=(100 to 450 by 50) minor=none; axis2 label=none value=none origin=(20pct,22pct) offset=(4pct,4pct); axis3 label=none; footnote1 'Fiscal Year'; footnote2 h=.5 ' '; pattern1 value=solid color=cx7c95ca; pattern2 value=solid color=cxde7e6f; proc gchart data=ds2; vbar mid / sumvar=resp group=year coutline=black patternid=midpoint space=0 gspace=5 width=4 cframe=ltgray autoref clipref raxis=axis1 maxis=axis2 gaxis=axis3 annotate=anno; run; quit;

Mark7 · ‎03-03-2020

Hi all, I am using sas code I found to generate every month graph. Problem showed up this month with new month "ERROR: The bottom horizontal axis labeled "year_month" could not be fit. There was not enough room in the graph's display area to fit the group axis." Where should I start changing this code? Attached last month graph. for table year_month int con 2019/09 8 5 2019/10 16 7 2019/11 25 11 2019/12 27 17 2020/01 36 22 2020/02 47 27 2020/03 47 28 data edanno; set hfedroll1; by year_month mid; length function color $8 text $20 style $ 20; /* Populate the table */ if first.year_month then do; function='move'; xsys='2'; ysys='1'; midpoint=mid; group=year_month; y=0; output; function='cntl2txt'; output; function='label'; xsys='A'; ysys='3'; x=+4; y=13; text=trim(left(put(int,8.))); color='black'; position='+'; when='a'; output; function='move'; xsys='2'; ysys='1'; midpoint=mid; group=year_month; y=0; output; function='cntl2txt'; output; function='label'; xsys='A'; ysys='3'; x=+4; y=9; text=trim(left(put(con,8.))); color='black'; position='+'; when='a'; output; end; /* Generate the table frame */ function='move'; xsys='3'; ysys='3'; x=3; y=6; output; function='bar'; xsys='1'; ysys='3'; x=100; y=15; style='empty'; color='black'; line=0; output; /* Generate the row headers */ function='label'; xsys='3'; ysys='3'; style='marker'; text='U'; color='cx7c95ca'; x=4; y=13; position='6'; output; function='label'; xsys='3'; ysys='3'; style='"Albany AMT"'; text='Intervention'; color='black'; x=7; y=13; position='6'; output; function='label'; xsys='3'; ysys='3'; style='marker'; text='U'; color='cxde7e6f'; x=4; y=8.5; position='6'; output; function='label'; xsys='3'; ysys='3'; style='"Albany AMT"'; text='Control'; color='black'; x=7; y=9; position='6'; output; /* Generate the vertical lines in the table */ function='move'; xsys='1'; ysys='1'; x=0; y=0; output; function='draw'; xsys='1'; ysys='3'; x=0; y=6; line=1; color='black'; output; function='move'; xsys='1'; ysys='1'; x=100; y=0; output; function='draw'; xsys='1'; ysys='3'; x=100; y=6; line=1; color='black'; output; if first.year_month and n ^=&skip then do; function='move'; xsys='2'; ysys='1'; midpoint=mid; y=0; group=year_month; output; function='move'; xsys='A'; ysys='1'; x=+15.5; x=+12; y=0; output; function='draw'; xsys='A'; ysys='3'; x=+0; y=6; color='black'; line=1; output; end; /* Generate the horizontal line in the table */ function='move'; xsys='3'; ysys='3'; x=3; y=10.5; output; function='draw'; xsys='1'; ysys='3'; x=100; y=10.5; line=1; color='black'; output; run; axis1 label=(a=90 'Nr of encounters') order=(0 to 100 by 10) minor=none; axis2 label=none value=none origin=(20pct,22pct) offset=(4pct,4pct); axis3 label=none; footnote1 'year_month'; footnote2 h=.5 ' '; pattern1 value=solid color=cx7c95ca; pattern2 value=solid color=cxde7e6f; proc gchart data=hfedroll1; vbar mid / sumvar=resp group=year_month coutline=black patternid=midpoint space=0 gspace=5 width=4 cframe=ltgray autoref clipref raxis=axis1 maxis=axis2 gaxis=axis3 annotate=edanno; run; quit;

Mark7 · ‎10-08-2019

Something like this? proc sort data=example; by Id group DateTime; run; data get_last; Set example; by ID group DateTime; if last.group ; run;

Mark7 · ‎10-08-2019

I am SAS EG drag and drop user ana found a problem to get proper solution I prepared dataset data example; input ID $ DateTime group $ colour $; informat DateTime datetime19. ; format DateTime datetime19. ; datalines; Z67897F 11SEP18:06:12:00 Fiat white Z67897F 26Nov18:12:26:00 Fiat red Z67897F 01May16:12:26:00 Ford black Z67897F 01May16:12:48:00 Ford purple D45671Z 26Dec17:09:26:00 Toyota yellow D45671Z 26Dec17:10:26:00 Toyota black D45671Z 26Jan19:09:26:00 Buick purple Z678B7F 09Jun18:09:26:00 Lexus white ; run; any hint how I can get ID DateTime group colour D45671Z 26Jan2019 9:26:00 Buick purple D45671Z 26Dec2017 10:26:00 Toyota black Z67897F 26Nov2018 12:26:00 Fiat red Z67897F 01May2016 12:48:00 Ford purple Z678B7F 09Jun2018 9:26:00 Lexus white

Mark7 · ‎10-07-2019

It is only for management, display table. Table contains over 20 mln rows

Mark7 · ‎10-07-2019

Hi I have dataset with ID, datetime, group, colour ID datetime group colour Z67897F 26Nov2017:09:26:00 Fiat white Z67897F 26Nov2018:12:26:00 Fiat red Z67897F 01May2016:12:26:00 Ford black Z67897F 01May2016:12:48:00 Ford purple D45671Z 26Dec2017:09:26:00 Toyota yellow D45671Z 26Dec2017:10:26:00 Toyota black D45671Z 26Jan2019:09:26:00 Buick purple . . Z678B7F 09Jun2018:09:26:00 Lexus white I can very easy to click and drag this report in EG. But is a way to get result using data step, to get for every ID one row with last result for each group and colour I have many datasets and number of groups differs. Is a way to get as many columns as number of groups? EG ID Fiat last datetime colour ford_last_datetime colour Toyota last dateti colour Z67897F 26Nov2018:12:26:00 red 01May2016:12:48:00 purple Null Null D45671Z Null Null Null Null 26Dec2017:10:26:00 black ....

Mark7 · ‎12-04-2018

Thank you learsaas. You are very helpful. Everything works great. I owe you so much

Mark7 · ‎12-04-2018

Hi draycut Yes I do have SAS/ETS

Mark7 · ‎12-03-2018

Hi All, I am trying calculate rolling 150 days sum and then set a flag 'Y' if the sum equals to 6 or more. In next column I am trying to flag 'Y' when previous product was C no matter what was next A, B or C irrelevant is ‘spend’. In third column important is to set up flag when between two dates is 4 or less days irrelevant is ‘spend’. Similarly for next id’s as well. Thank you id date prod spend rolling_spend flag within 150 days months sum equals to 6 or more if 25 days from previous C 4 days between any A, B C 1 11Dec2017 A 1 1 1 12Jan2018 B 2 3 1 23Apr2018 B 3 6 Y 1 31May2018 C 1 6 Y 1 12Jun2018 B 2 6 Y Y 1 23Jul2018 A 3 9 Y 1 31Jul2018 C 1 10 Y 1 12Aug2018 B 2 12 Y Y 1 23Aug2018 B 3 15 Y Y 1 31Aug2018 C 1 16 Y 1 12Nov2018 B 2 12 Y 1 23Dec2018 C 3 12 Y 1 31Dec2018 C 1 12 Y Y 2 01Mar2018 B 1 1 2 11Apr2018 B 1 2 2 21May2018 C 1 3 2 11Aug2018 A 2 4 2 21Aug2018 C 1 5 2 01Sep2018 C 1 6 Y Y 3 11May2018 C 1 1 3 12May2018 B 1 2 Y Y 3 21Jun2018 A 1 3 3 01Sep2018 A 1 4 3 11Sep2018 C 1 5 3 12Oct2018 B 1 4 3 21Nov2018 A 1 4 3 01Dec2018 C 1 5 I found a similar topic and I managed to do the first column but I have a problem with the next ones data have ; input id date : date10. prod $ spend ; format date date9.; CARDS; 1 11Dec2017 A 1 1 12Jan2018 B 2 1 23Apr2018 B 3 1 31May2018 C 1 1 12Jun2018 B 2 1 23Jul2018 A 3 1 31Jul2018 C 1 1 12Aug2018 B 2 1 23Aug2018 B 3 1 31Aug2018 C 1 1 12Nov2018 B 2 1 23Dec2018 B 3 1 31Dec2018 C 1 2 01Mar2018 B 1 2 11Apr2018 B 1 2 21May2018 C 1 2 11Aug2018 A 2 2 21Aug2018 C 1 2 01Sep2018 C 1 3 11May2018 C 1 3 12May2018 B 1 3 21Jun2018 A 1 3 01Sep2018 A 1 3 11Sep2018 C 1 3 12Oct2018 B 1 3 21Nov2018 A 1 3 01Dec2018 C 1 ;;;; proc sql; create table want as select t1.id, t1.date, t1.spend, sum(t2.spend) as Rolling_Spend, case when sum(t2.spend) >=6 then 'Y' end as Flag from have t1 left join have t2 on t1.id=t2.id and intck('day',t2.date,t1.date) between 0 and 150 group by t1.id, t1.date, t1.spend order by t1.id, t1.date; quit;

Online Status	Offline
Date Last Visited	‎04-21-2023 05:28 AM

Re: altering over 400 tables clustered in DI (SPDS tables)

Re: altering over 400 tables clustered in DI (SPDS tables)

altering over 400 tables clustered in DI (SPDS tables)

Re: The bottom horizontal axis labeled "year_month" could not be fit

Re: The bottom horizontal axis labeled "year_month" could not be fit

The bottom horizontal axis labeled "year_month" could not be fit

Re: last observation to keep

Re: last observation to keep

Re: last observation to keep

last observation to keep

Re: altering over 400 tables clustered in DI (SPDS tables)

Re: The bottom horizontal axis labeled "year_month" could not be fit

Re: altering over 400 tables clustered in DI (SPDS tables)

Re: altering over 400 tables clustered in DI (SPDS tables)

altering over 400 tables clustered in DI (SPDS tables)

Re: The bottom horizontal axis labeled "year_month" could not be fit

Re: The bottom horizontal axis labeled "year_month" could not be fit

The bottom horizontal axis labeled "year_month" could not be fit

Re: last observation to keep

Re: last observation to keep

Re: last observation to keep

last observation to keep

Re: SUM Based

Re: SUM Based

SUM Based