Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Home
- /
- About VCucu
11-20-2021
VCucu
Obsidian | Level 7
Member since
07-21-2020
- 31 Posts
- 12 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by VCucu
Subject Views Posted 2704 07-17-2021 12:45 PM 2718 07-17-2021 12:32 PM 2734 07-17-2021 12:28 PM 2738 07-17-2021 12:25 PM 2753 07-17-2021 12:09 PM 2772 07-17-2021 11:51 AM 2793 07-17-2021 10:51 AM 1298 04-30-2021 04:35 AM 998 04-29-2021 07:09 AM 1006 04-29-2021 06:59 AM -
Activity Feed for VCucu
- Posted Re: Proc sql within macro - where clause take values from macro variable containing list of values on SAS Programming. 07-17-2021 12:45 PM
- Posted Re: Proc sql within macro - where clause take values from macro variable containing list of values on SAS Programming. 07-17-2021 12:32 PM
- Posted Re: Proc sql within macro - where clause take values from macro variable containing list of values on SAS Programming. 07-17-2021 12:28 PM
- Posted Re: Proc sql within macro - where clause take values from macro variable containing list of values on SAS Programming. 07-17-2021 12:25 PM
- Posted Re: Proc sql within macro - where clause take values from macro variable containing list of values on SAS Programming. 07-17-2021 12:09 PM
- Posted Re: Proc sql within macro - where clause take values from macro variable containing list of values on SAS Programming. 07-17-2021 11:51 AM
- Posted Proc sql within macro - where clause take values from macro variable containing list of values on SAS Programming. 07-17-2021 10:51 AM
- Liked Re: Proc Report Colum Reference for Kurt_Bremser. 05-03-2021 03:21 AM
- Liked Re: Proc Report Colum Reference for PaigeMiller. 05-03-2021 03:21 AM
- Posted Re: Proc Report Colum Reference on SAS Programming. 04-30-2021 04:35 AM
- Posted Re: Compare values from one table to another on SAS Programming. 04-29-2021 07:09 AM
- Posted Compare values from one table to another on SAS Programming. 04-29-2021 06:59 AM
- Posted Re: Proc Report Colum Reference on SAS Programming. 04-29-2021 06:51 AM
- Posted Proc Report Colum Reference on SAS Programming. 04-29-2021 06:44 AM
- Posted Update colum in dataset based on comparison of 2 other columns on SAS Programming. 09-10-2020 05:25 PM
- Posted Re: Reference to a variable dataset name within a macro on SAS Programming. 09-07-2020 03:02 PM
- Liked Re: Reference to a variable dataset name within a macro for Tom. 09-07-2020 03:01 PM
- Posted Re: Reference to a variable dataset name within a macro on SAS Programming. 09-07-2020 09:52 AM
- Posted Re: Reference to a variable dataset name within a macro on SAS Programming. 09-07-2020 09:50 AM
- Posted Reference to a variable dataset name within a macro on SAS Programming. 09-07-2020 09:29 AM
-
Posts I Liked
Subject Likes Author Latest Post 1 1 1 1 1
09-07-2020
09:50 AM
1) thats fine, it was for illustration purpose; i have another one which is not "do" in the real application
2) i basically want to refer in a macro to a dataset name which is composed of two parts
- fixed text "prod"
- variable text coming from macro variable "xxx", "yyy", "zzz"
in the example provided using "prod&tenant." does not work
... View more
09-07-2020
09:29 AM
i have a dataset "distincttenants" with following values: xxx yyy zzz I would like to be able to refer correctly in the macro "do" to the datasets prodxxx, prodyyy, prodzzz. How do I need to write the code to reference to the correct dataset ? obviously the text "prod" together (&) with the macro variable which i tried in the example below does not work. thank you
%macro do(tenant=); proc sql; create table a1 as select * from prod&tenant.; quit; %mend do; data distincttenants; set distincttenants; str=catt('%do(tenant=',tenantid,');'); call execute(str); run;
... View more
08-21-2020
06:04 AM
Hi, issues solved. original code provided works also for larger datasets. could i ask you two follow-up questions pls: 1) how can i modify your above program so that it takes also NULL/BLANK value into account in the want dataset ? (it is the case that some variables have blanks i.e. instead of var1 taking the values A,B,C there might be some blanks which i would like to get under the name variable value "NULL" that is to have in the end "A","B","C" and "NULL") 2) in the dataset i have also a long name for variables values ; can i somehow move this as well to the want dataset ? example below: have: tenant generation var1 var1longname var2 var2longname metric_value 196 202007 A AAA X XXX 100 196 202007 B BBB Y YYY 50 196 202007 A BBB Y YYY 100 196 202007 B BBB Y YYY 200 196 202007 C CCC Z ZZZ 300 want: Tenant Generation variable variable_value variable_value_longname Metric_Value 196 202007 Var1 A AAA 200 196 202007 Var1 B BBB 250 196 202007 Var1 C CCC 300 196 202007 Var2 X XXX 100 196 202007 Var2 Y YYY 350 196 202007 Var2 Z ZZZ 300
... View more
08-20-2020
09:26 AM
thank you for the proposals regarding proc transpose; unfortunately it works perfectly only with small datasets; as soon as i try it with larger datasets (>10 million rows) I get the following error: is there any way to do this ? Thank you
... View more
08-20-2020
09:23 AM
thank you for the proposals regarding proc transpose; unfortunately it works perfectly only with small datasets; as soon as i try it with larger datasets (>10 million rows) I get the following error: is there any way to do this ? Thank you
... View more
08-20-2020
07:37 AM
the var1,var2 ... varz are around 30 and are known beforehand. However the values that each variable (var1,var2,varz) can take for each tenant and/or generation differ and should be read on the fly from the dataset.
... View more
08-20-2020
07:19 AM
Hi draycut, for example INPUT Tenant Generation Var1 Var2 Var3 … VarZ Metric_Value 196 202007 A X 100 196 202007 B Y 50 196 202007 A Y 100 196 202007 B Y 200 196 202007 C Z 300 196 202006 etc. OUTPUT Tenant Generation Variable Variable Value sum(Metric_Value) 196 202007 Var1 A 200 196 202007 Var1 B 250 196 202007 Var1 C 300 196 202007 Var2 X 100 196 202007 Var2 Y 350 196 202007 Var2 Z 300 196 202006 etc
... View more
08-20-2020
07:03 AM
Hello, I have an existing SAS dataset with the following structure: Tenant Generation Var1 Var2 Var3 … VarZ Metric_Value 196 202007 A X 100 196 202007 B Y 200 196 202007 C Z 300 196 202006 etc. I would like to process it as efficiently as possible (time-wise) to this summarized structure: Tenant Generation Variable Variable Value sum(Metric_Value) 196 202007 Var1 A 196 202007 Var1 B 196 202007 Var1 C 196 202007 Var2 X 196 202007 Var2 Y 196 202007 Var2 Z I currently do this by going for each variable through the whole dataset separately (takes a lot of time as there are many variables) :(. Is there a possibility to create the above resulting table only by going once through the dataset ? Thank you very much,
... View more
08-11-2020
08:32 AM
even more strange: if i put each macro in a separate program, so html3 in a program, and html9 in another program they both work with no problems... as soon as both macros are in the same program the first one works, the second one gets the respective error.
... View more
08-11-2020
08:16 AM
this is the complete code: i can run macro html3 as soon as i add another one (in this case macro html9) i get the error /* <<< Analysis for Report No. 3 >>> */ %macro html3(md=,var=,curr=,prev=); proc sql; create table a3 as select * from sasuser.production where branchID ="&var" and mandantid="&md" and generation in ("&curr","&prev"); quit; proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="&curr" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name; quit; proc sql; create table a3_2 as select branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="&prev" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name; quit; proc sql; create table a3_3 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart, a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on x.dealid=y.dealid; quit; proc sql; create table a3_4 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart, a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid; quit; proc sql; create table a3_5 as select a3_2.branchid, a3_2.customername, a3_2.dealid, a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid; quit; proc append base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run; data a3_5; set a3_5; array change _numeric_; do over change; if change=. then change=0; end; run ; proc sql outobs=100; create table a3_6 as select branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding, PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc; quit; options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production Report\report_196_analysis3_&var..html"; options nobyline; title "Drill Down Branches - Tenant ID=&md. & Branch ID=&var."; footnote "top 100 observations sorted by deviation descending"; proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid; column (obs branchid customername dealid dealstart dealend riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation); define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID'; define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product Name'; define previousoutstanding / display f=comma20. "Outstanding &prev."; define currentoutstanding / display f=comma20. "Outstanding &curr."; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20. style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed ; compute dev; dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run; options byline; title2; ods html close; %mend html3; /* <<< Analysis for Report No. 9 >>> */ %macro html9(md=,var=,curr=,prev=); proc sql; create table a9 as select * from sasuser.production where exposureid ="&var" and mandantid="&md" and generation in ("&curr","&prev"); quit; proc sql; create table a9_1 as select exposureid,generation,customername, dealid, dealstart, dealend,Riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a9 where generation="&curr" group by exposureid, generation,customername, dealid, dealstart, dealend,Riskproduct1name; quit; proc sql; create table a9_2 as select exposureid,generation,customername, dealid, dealstart, dealend,Riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a9 where generation="&prev" group by exposureid,generation,customername, dealid, dealstart, dealend,Riskproduct1name; quit; proc sql; create table a9_3 as select a9_1.exposureid, a9_1.customername, a9_1.dealid, a9_1.dealstart, a9_1.dealend, a9_1.Riskproduct1name, a9_1.generation as CurrentGeneration, a9_1.Eoutstanding1 as CurrentOutstanding format comma20., a9_2.generation as PreviousGeneration, a9_2.Eoutstanding1 as PreviousOutstanding format comma20. from a9_1 x inner join a9_2 y on x.dealid=y.dealid; quit; proc sql; create table a9_4 as select a9_1.exposureid, a9_1.customername, a9_1.dealid, a9_1.dealstart, a9_1.dealend, a9_1.Riskproduct1name, a9_1.generation as CurrentGeneration, a9_1.Eoutstanding1 as CurrentOutstanding format comma20., a9_2.generation as PreviousGeneration, a9_2.Eoutstanding1 as PreviousOutstanding format comma20. from a9_1 x left join a9_2 y on x.dealid=y.dealid where x.dealid<>y.dealid; quit; proc sql; create table a9_5 as select a9_2.exposureid, a9_2.customername, a9_2.dealid, a9_2.dealstart, a9_2.dealend, a9_2.Riskproduct1name, a9_1.generation as CurrentGeneration, a9_1.Eoutstanding1 as CurrentOutstanding format comma20., a9_2.generation as PreviousGeneration, a9_2.Eoutstanding1 as PreviousOutstanding format comma20. from a9_1 x right join a9_2 y on x.dealid=y.dealid where x.dealid<>y.dealid; quit; proc append base=a9_5 data=a9_4; run; proc append base=a9_5 data=a9_3; run; data a9_5; set a9_5; array change _numeric_; do over change; if change=. then change=0; end; run ; proc sql outobs=100; create table a9_6 as select exposureid, customername, dealid, dealstart, dealend, Riskproduct1name, CurrentGeneration, CurrentOutstanding, PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a9_5 order by deviation desc; quit; options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production Report\report_&md._analysis9_&var..html"; options nobyline; proc format; value colora -999999999999<-0='red' 0<-999999999999='green' other='white'; run; title "Drill Down Exposure Class - Tenant ID=&md. Exposure Class ID=&var."; footnote "top 100 observations sorted by absolute deviation descending"; proc report data=a9_6 nowd list headline headskip spacing=2 split='~'; column (obs exposureid customername dealid dealstart dealend Riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation); define exposureid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID'; define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define riskproduct1name / Display 'Risk Product Name'; define previousoutstanding / display f=comma20. "Outstanding &prev."; define currentoutstanding / display f=comma20. "Outstanding &curr."; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20. style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed ; compute dev; dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run; options byline; title2; ods html close; %mend html9; /* Create Macro Run Table */ proc datasets library=sasuser; delete prodrun; run; proc sql; create table temp as select distinct 'html3' as proc, mandantid, branchid as var, '202007' as curr, '202006' as prev from sasuser.production where branchid<>'?'; quit; proc append base=sasuser.prodrun data=temp; run; proc sql; create table temp as select distinct 'html9' as proc, mandantid, exposureid as var,'202007' as curr, '202006' as prev from sasuser.production; quit; proc append base=sasuser.prodrun data=temp; run; /* Execute Macro Run Table */ data _null_; set sasuser.prodrun; str=catt('%',proc,'(md=',mandantid,',var=',var,',curr=',curr,',prev=',prev,');'); call execute(str); run;
... View more
08-11-2020
08:12 AM
please see below log results. the first macro works (html3) while for the second (html9) i get the respective error. 1 Das SAS System
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='HTML Export Level 3';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='L:\Daten\Work\SAS Production Report\HTML Export Level 3.sas';
9 %LET _SASPROGRAMFILEHOST='W0196Z01095';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HTMLBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS94/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 data _null_;
27 set sasuser.prodrun;
28 str=catt('%',proc,'(md=',mandantid,',var=',var,',curr=',curr,',prev=',prev,');');
29 call execute(str);
30 run;
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: There were 25 observations read from the data set SASUSER.PRODRUN.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
2 Das SAS System
NOTE: CALL EXECUTE generated line.
1 + proc sql;
1 + create table a3 as select * from sasuser.production where branchID ="DE" and mandantid="196" and generation
in ("202007","202006");
NOTE: Table WORK.A3 created, with 1 rows and 59 columns.
1 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 51.18 seconds
cpu time 9.54 seconds
1 +
proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,
2 + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 0 rows and 8 columns.
2 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
2 +
proc sql; create table a3_2 as select
3 + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 1 rows and 8 columns.
3 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
3 +
proc sql;
4 + create table a3_3 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart, a3_1.dealend,
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a3_2.generation as
5 + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 0 rows and 10 columns.
5 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
5 +
proc sql; create table a3_4 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart,
3 Das SAS System
6 + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding
format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left
join
7 + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 0 rows and 10 columns.
7 + quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.07 seconds
cpu time 0.06 seconds
7 + proc sql; create table a3_5 as select a3_2.branchid,
a3_2.customername, a3_2.dealid, a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,
8 + a3_1.Eoutstanding1 as CurrentOutstanding format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 1 rows and 10 columns.
8 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.09 seconds
cpu time 0.06 seconds
8 +
proc append
9 + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run; data a3_5; set a3_5; array change _numeric_;
do over change; if change=. then change=0; end; run ; proc sql outobs=100; create table a3_6 as select
NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 0 observations read from the data set WORK.A3_4.
NOTE: 0 observations added.
NOTE: The data set WORK.A3_5 has 1 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 0 observations read from the data set WORK.A3_3.
NOTE: 0 observations added.
NOTE: The data set WORK.A3_5 has 1 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds
NOTE: There were 1 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 1 observations and 10 variables.
NOTE: DATA statement used (Total process time):
real time 0.08 seconds
cpu time 0.07 seconds
4 Das SAS System
10 + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding,
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
NOTE: Table WORK.A3_6 created, with 1 rows and 11 columns.
10 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.15 seconds
cpu time 0.11 seconds
11 + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_DE.html"; options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=DE"; footnote "top
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_DE.html
12 + sorted by deviation descending"; proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid;
column (obs branchid customername dealid dealstart dealend riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
13 + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID';
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product
Name'; define
14 + previousoutstanding / display f=comma20. "Outstanding 202006"; define currentoutstanding / display f=comma20.
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
15 + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed ; compute dev;
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run; options byline; title2;
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60 SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
) );
DEFINE obs / COMPUTED FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "obs" ;
DEFINE BranchID / GROUP FORMAT= $3. WIDTH=3 SPACING=2 NOPRINT LEFT "BranchID" ;
DEFINE CustomerName / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Customer Name" ;
DEFINE DealID / DISPLAY FORMAT= $30. WIDTH=30 SPACING=2 LEFT "Deal ID" ;
DEFINE DealStart / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal Start" ;
DEFINE DealEnd / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal End" ;
DEFINE RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Risk Product Name" ;
DEFINE PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202006" ;
DEFINE CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202007" ;
DEFINE dev / COMPUTED FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Relative Deviation" ;
DEFINE deviation / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Absolute Deviation" ;
COMPUTE obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;
COMPUTE dev;
dev = currentoutstanding - previousoutstanding;
ENDCOMP;
RUN;
NOTE: There were 1 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
5 Das SAS System
real time 0.28 seconds
cpu time 0.04 seconds
16 + proc sql;
16 + create table a3 as select * from sasuser.production where branchID ="HK" and mandantid="196" and generation
in ("202007","202006");
NOTE: Table WORK.A3 created, with 1635 rows and 59 columns.
16 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 55.34 seconds
cpu time 11.84 seconds
16 +
proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,
17 + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 561 rows and 8 columns.
17 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.08 seconds
cpu time 0.07 seconds
17 +
proc sql; create table a3_2 as select
18 + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 521 rows and 8 columns.
18 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.07 seconds
cpu time 0.12 seconds
18 +
proc sql;
19 + create table a3_3 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart, a3_1.dealend,
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a3_2.generation as
20 + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 494 rows and 10 columns.
20 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
20 +
6 Das SAS System
proc sql; create table a3_4 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart,
21 + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding
format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left
join
22 + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 67 rows and 10 columns.
22 + quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds
22 + proc sql; create table a3_5 as select a3_2.branchid,
a3_2.customername, a3_2.dealid, a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,
23 + a3_1.Eoutstanding1 as CurrentOutstanding format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 27 rows and 10 columns.
23 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds
23 +
proc append
24 + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run; data a3_5; set a3_5; array change _numeric_;
do over change; if change=. then change=0; end; run ; proc sql outobs=100; create table a3_6 as select
NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 67 observations read from the data set WORK.A3_4.
NOTE: 67 observations added.
NOTE: The data set WORK.A3_5 has 94 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 494 observations read from the data set WORK.A3_3.
NOTE: 494 observations added.
NOTE: The data set WORK.A3_5 has 588 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 588 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 588 observations and 10 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
7 Das SAS System
25 + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding,
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
WARNING: Statement terminated early due to OUTOBS=100 option.
NOTE: Table WORK.A3_6 created, with 100 rows and 11 columns.
25 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.07 seconds
cpu time 0.07 seconds
26 + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_HK.html"; options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=HK"; footnote "top
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_HK.html
27 + sorted by deviation descending"; proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid;
column (obs branchid customername dealid dealstart dealend riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
28 + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID';
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product
Name'; define
29 + previousoutstanding / display f=comma20. "Outstanding 202006"; define currentoutstanding / display f=comma20.
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
30 + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed ; compute dev;
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run; options byline; title2;
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60 SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
) );
DEFINE obs / COMPUTED FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "obs" ;
DEFINE BranchID / GROUP FORMAT= $3. WIDTH=3 SPACING=2 NOPRINT LEFT "BranchID" ;
DEFINE CustomerName / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Customer Name" ;
DEFINE DealID / DISPLAY FORMAT= $30. WIDTH=30 SPACING=2 LEFT "Deal ID" ;
DEFINE DealStart / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal Start" ;
DEFINE DealEnd / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal End" ;
DEFINE RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Risk Product Name" ;
DEFINE PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202006" ;
DEFINE CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202007" ;
DEFINE dev / COMPUTED FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Relative Deviation" ;
DEFINE deviation / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Absolute Deviation" ;
COMPUTE obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;
COMPUTE dev;
dev = currentoutstanding - previousoutstanding;
ENDCOMP;
RUN;
8 Das SAS System
NOTE: There were 100 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
real time 0.49 seconds
cpu time 0.12 seconds
31 + proc sql;
31 + create table a3 as select * from sasuser.production where branchID ="LO" and mandantid="196" and generation
in ("202007","202006");
NOTE: Table WORK.A3 created, with 310 rows and 59 columns.
31 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 50.38 seconds
cpu time 9.96 seconds
31 +
proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,
32 + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 78 rows and 8 columns.
32 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
32 +
proc sql; create table a3_2 as select
33 + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 104 rows and 8 columns.
33 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
33 +
proc sql;
34 + create table a3_3 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart, a3_1.dealend,
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a3_2.generation as
35 + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 70 rows and 10 columns.
35 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.05 seconds
9 Das SAS System
cpu time 0.04 seconds
35 +
proc sql; create table a3_4 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart,
36 + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding
format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left
join
37 + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 8 rows and 10 columns.
37 + quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
37 + proc sql; create table a3_5 as select a3_2.branchid,
a3_2.customername, a3_2.dealid, a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,
38 + a3_1.Eoutstanding1 as CurrentOutstanding format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 34 rows and 10 columns.
38 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
38 +
proc append
39 + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run; data a3_5; set a3_5; array change _numeric_;
do over change; if change=. then change=0; end; run ; proc sql outobs=100; create table a3_6 as select
NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 8 observations read from the data set WORK.A3_4.
NOTE: 8 observations added.
NOTE: The data set WORK.A3_5 has 42 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 70 observations read from the data set WORK.A3_3.
NOTE: 70 observations added.
NOTE: The data set WORK.A3_5 has 112 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 112 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 112 observations and 10 variables.
10 Das SAS System
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
40 + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding,
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
WARNING: Statement terminated early due to OUTOBS=100 option.
NOTE: Table WORK.A3_6 created, with 100 rows and 11 columns.
40 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.07 seconds
cpu time 0.07 seconds
41 + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_LO.html"; options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=LO"; footnote "top
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_LO.html
42 + sorted by deviation descending"; proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid;
column (obs branchid customername dealid dealstart dealend riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
43 + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID';
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product
Name'; define
44 + previousoutstanding / display f=comma20. "Outstanding 202006"; define currentoutstanding / display f=comma20.
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
45 + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed ; compute dev;
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run; options byline; title2;
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60 SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
) );
DEFINE obs / COMPUTED FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "obs" ;
DEFINE BranchID / GROUP FORMAT= $3. WIDTH=3 SPACING=2 NOPRINT LEFT "BranchID" ;
DEFINE CustomerName / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Customer Name" ;
DEFINE DealID / DISPLAY FORMAT= $30. WIDTH=30 SPACING=2 LEFT "Deal ID" ;
DEFINE DealStart / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal Start" ;
DEFINE DealEnd / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal End" ;
DEFINE RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Risk Product Name" ;
DEFINE PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202006" ;
DEFINE CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202007" ;
DEFINE dev / COMPUTED FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Relative Deviation" ;
DEFINE deviation / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Absolute Deviation" ;
COMPUTE obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;
COMPUTE dev;
dev = currentoutstanding - previousoutstanding;
11 Das SAS System
ENDCOMP;
RUN;
NOTE: There were 100 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
real time 0.46 seconds
cpu time 0.14 seconds
46 + proc sql;
46 + create table a3 as select * from sasuser.production where branchID ="NY" and mandantid="196" and generation
in ("202007","202006");
NOTE: Table WORK.A3 created, with 1088 rows and 59 columns.
46 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 51.29 seconds
cpu time 10.12 seconds
46 +
proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,
47 + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 355 rows and 8 columns.
47 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.05 seconds
cpu time 0.01 seconds
47 +
proc sql; create table a3_2 as select
48 + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 356 rows and 8 columns.
48 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
48 +
proc sql;
49 + create table a3_3 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart, a3_1.dealend,
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a3_2.generation as
50 + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 247 rows and 10 columns.
50 +
12 Das SAS System
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds
50 +
proc sql; create table a3_4 as select a3_1.branchid, a3_1.customername, a3_1.dealid, a3_1.dealstart,
51 + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding
format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left
join
52 + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 108 rows and 10 columns.
52 + quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.05 seconds
cpu time 0.06 seconds
52 + proc sql; create table a3_5 as select a3_2.branchid,
a3_2.customername, a3_2.dealid, a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,
53 + a3_1.Eoutstanding1 as CurrentOutstanding format comma20., a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 109 rows and 10 columns.
53 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.05 seconds
cpu time 0.06 seconds
53 +
proc append
54 + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run; data a3_5; set a3_5; array change _numeric_;
do over change; if change=. then change=0; end; run ; proc sql outobs=100; create table a3_6 as select
NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 108 observations read from the data set WORK.A3_4.
NOTE: 108 observations added.
NOTE: The data set WORK.A3_5 has 217 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 247 observations read from the data set WORK.A3_3.
NOTE: 247 observations added.
NOTE: The data set WORK.A3_5 has 464 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
13 Das SAS System
NOTE: There were 464 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 464 observations and 10 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
55 + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding,
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
WARNING: Statement terminated early due to OUTOBS=100 option.
NOTE: Table WORK.A3_6 created, with 100 rows and 11 columns.
55 +
quit;
NOTE: PROZEDUR SQL used (Total process time):
real time 0.09 seconds
cpu time 0.09 seconds
56 + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_NY.html"; options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=NY"; footnote "top
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production
Report\report_196_analysis3_NY.html
57 + sorted by deviation descending"; proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid;
column (obs branchid customername dealid dealstart dealend riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
58 + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID';
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product
Name'; define
59 + previousoutstanding / display f=comma20. "Outstanding 202006"; define currentoutstanding / display f=comma20.
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
60 + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed ; compute dev;
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run; options byline; title2;
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60 SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
) );
DEFINE obs / COMPUTED FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "obs" ;
DEFINE BranchID / GROUP FORMAT= $3. WIDTH=3 SPACING=2 NOPRINT LEFT "BranchID" ;
DEFINE CustomerName / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Customer Name" ;
DEFINE DealID / DISPLAY FORMAT= $30. WIDTH=30 SPACING=2 LEFT "Deal ID" ;
DEFINE DealStart / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal Start" ;
DEFINE DealEnd / DISPLAY FORMAT= $20. WIDTH=20 SPACING=2 LEFT "Deal End" ;
DEFINE RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50 SPACING=2 LEFT "Risk Product Name" ;
DEFINE PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202006" ;
DEFINE CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Outstanding 202007" ;
DEFINE dev / COMPUTED FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Relative Deviation" ;
DEFINE deviation / DISPLAY FORMAT= COMMA20. WIDTH=20 SPACING=2 RIGHT "Absolute Deviation" ;
COMPUTE obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;
14 Das SAS System
COMPUTE dev;
dev = currentoutstanding - previousoutstanding;
ENDCOMP;
RUN;
NOTE: There were 100 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
real time 0.49 seconds
cpu time 0.12 seconds
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
61 + %html9(md=196,var=A,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
62 + %html9(md=196,var=C,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
63 + %html9(md=196,var=F,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
64 + %html9(md=196,var=I,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
65 + %html9(md=196,var=L,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
66 + %html9(md=196,var=N,curr=202007,prev=202006);
_
180
15 Das SAS System
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
67 + %html9(md=196,var=O,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
68 + %html9(md=196,var=S,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
69 + %html9(md=196,var=U,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
70 + %html9(md=196,var=X,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
71 + %html9(md=196,var=Y,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
72 + %html9(md=198,var=A,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
73 + %html9(md=198,var=C,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
16 Das SAS System
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
74 + %html9(md=198,var=F,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
75 + %html9(md=198,var=I,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
76 + %html9(md=198,var=L,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
77 + %html9(md=198,var=N,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
78 + %html9(md=198,var=O,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
79 + %html9(md=198,var=S,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
80 + %html9(md=198,var=U,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
WARNING: Apparent invocation of macro HTML9 not resolved.
17 Das SAS System
NOTE: Line generated by the CALL EXECUTE routine.
81 + %html9(md=198,var=Y,curr=202007,prev=202006);
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
31
32 GOPTIONS NOACCESSIBLE;
33 %LET _CLIENTTASKLABEL=;
34 %LET _CLIENTPROCESSFLOWNAME=;
35 %LET _CLIENTPROJECTPATH=;
36 %LET _CLIENTPROJECTPATHHOST=;
37 %LET _CLIENTPROJECTNAME=;
38 %LET _SASPROGRAMFILE=;
39 %LET _SASPROGRAMFILEHOST=;
40
41 ;*';*";*/;quit;run;
42 ODS _ALL_ CLOSE;
43
44
45 QUIT; RUN;
46
... View more
08-11-2020
06:57 AM
I have the following macro which I call using a data STEP: %macro htmlexport(md=,var=,curr=,prev=); proc sql; create table a3 as select * from sasuser.production where branchID ="&var" and mandantid="&md" and generation in ("&curr","&prev"); quit; %mend htmlexport; Call execute Macro Step: 1) Prepare list of variables: proc sql; create table temp as select distinct mandantid, branchid, '202007' as curr, '202006' as prev from sasuser.production where mandantid='196' and branchid<>'?'; quit; this creates the following table: 2) Running Call Execute data _null_; set temp; str=catt('%htmlexport(md=',mandantid,',var=',branchid,',curr=',curr,',prev=',prev,');'); call execute(str); run; However i get the following error and I don't understand what is wrong. It drives me crazy 🙂 does anybody has an idea what I am doing wrong with this call execute ? thank you very much
... View more
07-25-2020
11:00 AM
super. thank you very much for the feedback. 🙂 i will try the macro variant.
... View more
07-24-2020
03:25 PM
Hello, i have the following code for writing a report to HTML: " ods html file='C:\analysis1.html' newfile=page; proc report data=sales nowd list headline headskip spacing=2 split='~'; by branchid; column (branchid representative sales); define branchid / group noprint; define representative / display 'Representative Name'; define Sales / display f=comma20. 'Yearly Sales'; run; ods html close; " This creates for each change of branch ID in the report an incremental file e.g.: analysis1.html analysis2.html analysis3.html ... Is there any way to change the name of the output html file for each page dynamically? i would like to have a combination of 'analysis1' || trim(branchid) ||'.html' variable, resulting in analysis1HK.html (Hong Kong Branch) analysis1LO.html (London Branch) .... and so on for all branchid defined in the report. Is this possible somehow ? Thank you very much,
... View more
07-22-2020
06:15 AM
i would like a dataset like this. will use it for reporting in a later step with proc report. thank you
... View more
- « Previous
-
- 1
- 2
- Next »
Contact Me
| Online Status |
Offline
|
| Date Last Visited |
11-20-2021
07:06 AM
|
Likes given to
