topic Re: How to combine two proc sql table and then export them as ods html? in SAS Programming

How to combine two proc sql table and then export them as ods html?

Kayla_Tan222 — Mon, 01 Jul 2019 08:43:29 GMT

Hi all, is there a way to combine the proc sql table I created in my do loop?

Here's my code below for my do loop to produce 3 proc sql tables (which is 201705,201704,201703).

The codes below will produce 3 ods html. Instead of producing 3 ods html, after combining all the three proc sql table, I want to export them in ods html together so there will only be 1 ods html.

%macro date(YYYY=2017,MM=05,Y=2017,M=05);

(some coding....)

%macro financial (MONTH);

%do i=&acmthfy1. %to &MONTH;

%if &i.=1 %THEN %DO;

%let fileyear=%eval(&YYYY-1);

%let filemth=12;

%end;

%else %if &i.=2 %THEN %DO;

%let fileyear=&YYYY;

%let filemth=01;

%end;

%else %if &i.=11 %then %do;

%let fileyear=&YYYY;

%let filemth=10;

%end;

%else %if &i.=12 %then %do;

%let fileyear=&YYYY;

%let filemth=11;

%end;

%else %do;

%let fileyear=&YYYY;

%let filemth=0%eval(&i.-1);

%end;

/*****************************PD*****************************/

PROC SQL;

create table SMCD_PD_&fileyear&filemth. AS

SELECT put(zmajrsk,$class.) as class, dev11yr_fy,sum(zclmpd) AS zclmpd

FROM pl&Y&M..POLA_clm_3months_&Y&M.

WHERE ACCYR_FY=&Y. & accmth_fy=&i.

GROUP BY class,2;

run;

ods html

file="\\kaiwksgh415thw5\Data\POLA\Claim\&fileyear\&fileyear&filemth\result\SMCD_mth_PD_FY_10DY_&fileyear&filemth..html"

style=normal;

proc print;

run;

%end;

%mend financial;

%financial(&acmthfy2.);

%mend date;

%date(YYYY=2017,MM=05,Y=2017,M=05);

run;

Re: How to combine two proc sql table and then export them as ods html?

Kurt_Bremser — Mon, 01 Jul 2019 09:21:55 GMT

Take the ods html out of the loop, so you create only one file.

BTW your code is unnecessarily complicated. Macro financial is not needed, it only obfuscates what you are doing.

Big hint: nested macro definitions serve no purpose, as all macros are always defined in the global symbol table. And a macro that is called only once within another macro is not necessary.

Re: How to combine two proc sql table and then export them as ods html?

Vish33 — Mon, 01 Jul 2019 14:27:45 GMT

I agree with @Kurt_Bremser , and still if you want to combine three SQL tables and put it together and generate ODS results use proc append procedure and close the macro . use the new data set in your ods results.

Proc append base=New data=SMCD_PD_&fileyear&filemth. ;

run;

i hope it helps!