Recently in the SAS Community Library: SAS' @StuartRogers provides a close look at the new Microsoft Entra Gallery application and details how it can be used.
I need to combine same clinic names if the corresponding prior_span =1.
For example, if Clinic_name1 and Clinic_name2 where both Clinic A and Prior_span1 and prior_span2 both =1 then Clinic would = A.
Clinic_name_1 corresponds with Prior_span1
Clinic_name_2 corresponds with Prior_span2
Clinic_name_3 corresponds with Prior_span3
Below is my sample data.
Thanks!
Data Have;
input
ID$ Clinic_Name_1$ Clinic_Name_2$ Clinic_Name_3$ Prior_Span1$ Prior_Span2$ Prior_Span3$ Cost service$ ;
datalines;
1 B A . 0 0 0 168 A
2 A A . 1 0 0 46 B
2 A A . 0 1 0 99 C
3 B . . 0 0 0 24 D
4 D . . 0 0 0 54 A
5 B D F 0 1 0 405 B
7 A . . 1 0 0 90 F
8 E E A 0 0 1 87 J
9 F . . 0 0 0 0 G
10 C C C 0 0 0 40 C
10 C C C 0 0 1 374 B
10 C C C 0 1 1 60 A
;
run;
***Summary table-clinic var not created yet****;
proc sql;
create table want as
select 1 as ID1,'service' as a length=40,Clinic,
1 as ID2,put(service, $40.) as b length=40,
1 as ID3,(select count(distinct ID) from have where Clinic=a.Clinic and service=a.service and cost>0 ) as c format=comma20.,
1 as ID4,calculated c/(select count(distinct ID) from have where Clinic=a.Clinic and cost>0 ) as d format=percent8.1,
1 as ID5,sum(cost) as e format=dollar20.,
1 as ID6,sum(cost)/(select sum(cost) from have where Clinic=a.Clinic) as f format=percent8.1
from have as a
group by Clinic,service
union all
select 7 as id1,'09'x as a,Clinic,
7 as id2,'Total Clients' as b,
7 as id3,count(distinct ID) as f,
7 as id4,.,
7 as id5,.,
7 as id7,.
from have
where service ne 'j' and cost>0
group by Clinic
union all
select 7 as id1,'09'x as a,Clinic,
7 as id2,' Total Cost' as f,
7 as id3,. as c,
7 as id4,.,
7 as id5,sum(cost),
7 as id7,.
from have
where service ne 'j' and cost>0
group by Clinic
;quit;
options missing=' ';
proc report data=want nowd;
column id1 a b Clinic,(c d e f);
define id1/group noprint;
define a/group noprint ' ';
define b/group ' ';
define c/analysis 'Clients';
define d/analysis '% of Clients';
define e/analysis 'Cost';
define f/analysis '% of Cost';
define Clinic/across '';
compute before a/style={just=l color=black};
if id1=1 then len=0;
else len=10;
x=' ';
line x $varying10. len;
line a $40.;
endcomp;
run;
... View more
I have been looking at this code syntax for hours, the Teradata code runs in a different environment, but bringing it through Enterprise Guide is giving me issues. The error I am getting is: ERROR: Teradata execute: Syntax error: expected something between ')' and ';'. I can't seem to figure out what the issue is. I appreciate any help or insight on this.
PROC SQL NOERRORSTOP;
CONNECT TO TERADATA (AUTHDOMAIN=xx server="xxx" connection=global mode=teradata);
execute (drop table TestServer.Test3) BY Teradata;
execute (create table TestServer.Test3 as
(
select *
from TestServer.Test1
UNION ALL
select *
from TestServer.Test2
))BY Teradata;
quit;
... View more
Hi, I have some code that sums up monthly values and assigns each month as a new column. The problem is the code is ugly and I feel like there should be a better way to do this. Here is the code:
PROC SQL;
CREATE TABLE TMP1DAY.MONTHLY_SUMMARY AS
SELECT t1.ID_VARIABLE,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201901" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201902", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201901,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201902" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201903", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201902,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201903" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201904", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201903,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201904" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201905", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201904,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201905" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201906", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201905,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201906" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201907", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201906,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201907" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201908", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201907,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201908" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201909", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201908,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201909" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201910", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201909,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201910" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201911", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201910,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201911" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201912", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201911,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201912" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "202001", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201912_01,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201912" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "202002", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201912_02,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202001" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202002", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202001,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202002" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202003", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202002,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202003" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202004", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202003,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202004" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202005", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202004,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202005" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202006", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202005,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202006" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202007", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202006,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202007" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202008", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202007,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202008" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202009", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202008,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202009" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202010", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202009,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202010" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202011", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202010,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202011" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202012", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202011,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202012" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202101", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202012_01,
SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202012" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202102", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202012_02
FROM WORK.PRE_MONTHLY_SUMMARY t1
GROUP BY 1;
QUIT;
How would I instead write this to do the same thing but programmatically/with macro variables? its already out of hand and I will need to add more years which will just make it even worse. Any and all advice is appreciated.
Thanks in advance!
... View more
Hello,
I am struggling with controlling page breaks in proc report/ods PDF. I already did a post a few months ago (https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-suppress-table-splitting-in-ODS-PDF/m-p/907292#M26446), but I have not found a solution yet. First, I thought I will just use ods rtf with the keepn option, but that brings more problems than it solves. Now, I have just found a paper that kind of describes the problem from Stetz et al. "Controlling Page Breaks when using Proc Report" (https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://support.sas.com/resources/papers/proceedings-archive/SUGI95/Sugi-95-70%2520Stetz.pdf&ved=2ahUKEwjHib_10PuFAxUi_rsIHa0GA9oQFnoECBAQAw&usg=AOvVaw0Z1J0znhm8-SeB_Oxq4tBr). Unfortunately, the paper is quite short and I do not understand how the data in the example code is structured. My question is: Does anyone have further knowledge on using "line counting" to control page breaks with proc report? Or does anyone know other papers that talk about this issue?
@Cynthia_sas did you write the paper? And if so, have you got any tips for the page break control?
If anyone needs code examples I can provide code and data, but for now I thought it would be more useful if I try different approaches before just posting my code where I did not achieve much yet. I am grateful for any help.
Kind regards
... View more
Hello,
I have the below code which is part of larger code that will stop running the rest of the program if there is a difference in the proc compare. It is working okay but I am curious if there is a way to rename the _type_ variable that is created for the comparison. Right now it list BASE and COMPARE which is great but for a user that isn't aware I would like to rename to the actual file names. Is this possible?
%let diff_count = 0;
/* Compare the TO_TAX and FROM_TAX BUWfiles */
PROC COMPARE BASE=WORK.TO_TAX_MODIFIED brief transpose COMPARE=WORK.FROM_TAX_MODIFIED OUT=COMPARISON OUTBASE OUTCOMP OUTDIF LISTOBS OUTNOEQUAL BRIEFSUMMARY;
VAR member_number account_number payer_id ;
title 'COMPARING FILE SENT AND RECEIVED FROM';
RUN;
DATA _NULL_;
SET COMPARISON END=eof;
IF eof THEN CALL SYMPUT('diff_count', _N_);
RUN;
... View more