Recently in the SAS Community Library: SAS' @BethEbersole reveals 4 steps to stop money laundering, solve law-enforcement cases, find missing children and more with SAS Visual Investigator.
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
I'm currently working on testing the overall Proportional Hazards Assumption for my 5 multiple imputation datasets.
My challenge lies in determining the overall p-value of the Supremum Test for Proportional Hazards Assumption. While the test only provides the Maximum Absolute Value and Pr > MaxAbsVal, I'm uncertain about how to derive the overall p-value from proc mianalyze.
Could you kindly provide some guidance or insights on how to approach this issue? Any assistance or pointers you could offer would be greatly appreciated.
Thank you very much for your time and assistance.
... View more