About Ronein

Ronein · ‎03-12-2025

Hello In data set ttt there are 135 rows (In each row there is a var name in field field_name) I am using this code to create a macro var that concatenate the var names I see in Log that the macro var has only concatenation of 75 values. WHY???? proc sql noprint; select field_name into : Vfields separated by ' ' from ttt ; quit; %put &Vfields;

Ronein · ‎02-27-2025

Hello I want to color in red the cells with value greater than 28 in 3 fields: EXPD RCVD RCVD_ALL what is the way to do it please via proc report? data test; input WD DAY $ EXPD RCVD RCVD_ALL NEWC $; cards; -1 31JUL 29 49 56 Y 01 3AUG 26 26 77 Y 02 4AUG 77 45 0 N 03 5AUG 71 0 0 N 04 6AUG 44 0 0 N ; RUN; proc report data=TEST nowd; column WD DAY EXPD RCVD RCVD_ALL; define WD / 'Working Day' display ; define DAY / ' Day' display; define EXPD / display; define RCVD / display; define RCVD_ALL / display; define NEWC/display; quit;

Ronein · ‎02-24-2025

Hello, I have the following problem- I have 2 data sets: Data set Have1 with columns: CustID (Customer ID) Loan_ID (Loan ID) and 12 months of follow up period that we need to check if customer is in failure Note- the dates are in structure YYMM (numeric field) Data set Have2 with columns: CustID (Customer ID) ,mon ( month in YYMM numeric structure), IND (indicator binary for failure) Target- I need to check for each CustID+Loan_ID if there is failure in the following up months only! How can I do it please? Please note that for customer 111 there are 2 loans and each loan has different follow up period Data have1; input CustID Loan_ID Mon1 Mon2 Mon3 Mon4 Mon5 Mon6 Mon7 Mon8 Mon9 Mon10 Mon11 Mon12; cards; 111 121212 2307 2308 2309 2310 2311 2312 2401 2402 2403 2404 2405 2406 111 333333 2309 2310 2311 2312 2401 2402 2403 2404 2405 2406 2407 2408 222 777777 2307 2308 2309 2310 2311 2312 2401 2402 2403 2404 2405 2406 ; Run; Data have2; input CustID mon Ind; cards; 111 2306 1 111 2307 0 111 2308 0 111 2309 0 111 2310 0 111 2311 0 111 2312 0 111 2401 0 111 2402 0 111 2403 0 111 2404 0 111 2405 0 111 2406 0 111 2407 1 111 2408 1 111 2409 1 111 2410 1 111 2411 1 111 2412 1 222 2306 0 222 2307 0 222 2308 0 222 2309 0 222 2310 0 222 2311 0 222 2312 0 222 2401 0 222 2402 0 222 2403 0 222 2404 0 222 2405 0 222 2406 0 222 2407 0 222 2408 0 222 2409 0 222 2410 0 222 2411 0 222 2412 0 ; Run; Data want; input CustID Loan_ID Max_Ind_in_FollowUp; cards; 111 121212 0 111 333333 1 222 777777 0 ; Run;

Ronein · ‎02-20-2025

Hello I want to calculate number of numeric missing values in each row. I want to calculate number of Char missing values in each row. Why the char missing values is not calculated correctly?? data my_data; input team $ points assists rebounds; cards; A 10 2 . A 17 5 . A 17 . . A 18 3 4 A 15 0 5 B . 4 5 B 29 0 8 B . 2 9 C 12 1 9 . 30 1 . ; run; data want; set my_data; nr_Numeric_Missing_Values=nmiss(of _numeric_)-1; nr_CHAR_Missing_Values=nmiss(of _character_)-1; Run;

Ronein · ‎02-20-2025

Thanks, Is location of RETAIN important? can it be above SET or below SET?

Ronein · ‎02-19-2025

Thank you so much. I just want to understand. In the data set there are 3 numeric vars (before adding the new Var). In obs=1 there is one missing value. In this code: as I understand check missing values in 3 Vars. (But you say that it also check missing in the new VAR??) And why the new var is also missing? nr_Numeric_Missing_Values=nmiss(of _numeric_);

Ronein · ‎02-19-2025

Hello I want to calculate number of missing numeric values for each row. Obs 1 has one missing value. Why the result get 2 missing values??? data my_data; input team $ points assists rebounds; cards; A 10 2 . A 17 5 . A 17 . . A 18 3 4 A 15 0 5 B . 4 5 B 29 0 8 B . 2 9 C 12 1 9 . 30 1 . ; run; data want; set my_data; nr_Numeric_Missing_Values=nmiss(of _numeric_); Run;

Ronein · ‎02-19-2025

Hello Please see following code. Some questions please- 1- Is this code check if data set exist or check if sas can open the data set? 2-What is the difference between the question if data set exist and if data set can be opened? As I understand If data set exist then sas can open it and if not exist then sas cannot open it because it doesnt exist 3- What is the meaning of %let rc=%sysfunc(close(&dsid)); Why is it not written as %let rc=%sysfunc(close(&tbl)); Does it mean that we want that SAS close the open data set? What is it beneficial to have this statement? %macro Check_DataSet_Exist(tbl); %let dsid=%sysfunc(open(&tbl,i)); %put dsid=&dsid; %if (&dsid=0) %then %put &tbl data set has NOT been opened; %else %put &tbl data set has been opened; %let rc=%sysfunc(close(&dsid)); %mend Check_DataSet_Exist; %Check_DataSet_Exist(tbl=sashelp.cars) %Check_DataSet_Exist(tbl=sashelp.weather)

Ronein · ‎02-19-2025

Hello In order to check if a data set has index I saw the following code: %let dsid=%sysfunc(open(sashelp.cars)); %put dsid=&dsid; /**2**/ %let indx=%sysfunc(attrn(&dsid,isindex)); %put indx=&indx; /**0**/ My question- &dsid get value 2 what does it mean? What exactly &dsid is calculating? In code : %let indx=%sysfunc(attrn(&dsid,isindex)); why the argument is &dsid and not data set name (sashelp.cars)? http://support.sas.com/kb/43/637.html

Ronein · ‎02-18-2025

I expect that WAY4 (t4) be the best, Am I right?

Ronein · ‎02-18-2025

I use it in order to select last row by group proc sort data=ACCOUNT_MONTHLY_DATA_RF_CS_12; by UPDATE_DATE FK_APPLICATION FK_MONTHLY_DATA_ACCOUNT REFERENCE_DATE; Run; Data ACCOUNT_MONTHLY_DATA_RF_CS_1(RENAME=(ACCOUNT_OPEN_DATE_=ACCOUNT_OPEN_DATE ACCOUNT_ID_OPEN_DATE_=ACCOUNT_ID_OPEN_DATE)); SET ACCOUNT_MONTHLY_DATA_RF_CS_12; by UPDATE_DATE FK_APPLICATION FK_MONTHLY_DATA_ACCOUNT REFERENCE_DATE ; If last.FK_MONTHLY_DATA_ACCOUNT; IF PRIMARY_ACCOUNT_INDICATOR=2 then Ind_Mishni=1; else Ind_Mishni=0; IF ACCOUNT_CHARACTER IN(30,38,57,60,12,88,96) then Ind_Tmura=1; else Ind_Tmura=0; ACCOUNT_OPEN_DATE_=DATEPART(ACCOUNT_OPEN_DATE); ACCOUNT_ID_OPEN_DATE_= DATEPART(ACCOUNT_ID_OPEN_DATE); format ACCOUNT_OPEN_DATE_ ACCOUNT_ID_OPEN_DATE_ date9.; ACCOUNT_AGE_MONTHS=ROUND(ACCOUNT_AGE*12); KEEP UTILIZATION_AMOUNT RISK_RATING Ind_Mishni Ind_Tmura FK_APPLICATION UPDATE_DATE REFERENCE_DATE ID FK_MONTHLY_DATA_ACCOUNT ACCOUNT_AGE ACCOUNT_AGE_MONTHS ACCOUNT_OPEN_DATE_ ACCOUNT_ID_OPEN_DATE_ ACCOUNT_BALANCE ACCOUNT_CHARACTER ACCOUNT_ID_BANK ACCOUNT_ID_DEPARTMENT ACCOUNT_SCORING_STATUS ACCOUNT_TYPE_NUMBER ATTRIBUTION_CHARACTER BCG BRANCH_NUMBER_MASTER CASH_DEPOSITS_AMOUNT CHECK_DEPOSITS_AMOUNT CHILDREN_ALLOWANCE CLIENT_ID_MASTER CREDIT_CARD_UTIL CREDIT_CARD_UTIL_CURRENT CUSTOMER_DOMAIN_CODE CUSTOMER_NUMBER_MASTER DATA_SOURCE DEBT_INDICATOR DIVISION_CODE ECONOMIC_SECTOR EXTERNAL_LOANS_AMOUNT FINANCIAL_WEALTH FINANCIAL_WEALTH_DAILY GUARANTEES_AMOUNT INTEGRATION_SALARY LINKED_ACCOUNT_IND LINKED_ACOUNT_ELIGIBLE_IND LOANS_BALANCE MONTHLY_CYCLE MONTHLY_LOAN_PAYMENT MONTHS_WITH_DATA_MASK NUM_ATM_CARD_BLOCKINGS NUM_CREDIT_CARD_BLOCKINGS NUM_MONTHS_WITH_DATA NUM_MORTGAGES NUM_OVERLIMIT_PAYMENTS NUM_REJECTED_PAYMENTS OBLIGO OBLIGO_BUSINESS OTHER_ALLOWANCE OVERDUE_AMOUNT OVERDUE_DAYS OVERDUE_TYPE OVERLIMIT_AMOUNT OVERLIMIT_DAYS OVERLIMIT_PAYMENTS_AMOUNT OWN_ACCOUNT_TRANSFERS_AMOUNT OWNERSHIP_CODE PENSION_AMOUNT POPULATION_CODE PRIMARY_ACCOUNT_INDICATOR REJECTED_PAYMENTS_AMOUNT RELATIONSHIP_TYPE REQ_INVESTIGATION_INDICATOR RESTRICTION_SOURSE RISK_FRAME_ORIGINAL SCORING_SUB_POPULATION SECURITIES_AMOUNT SECURITIES_TRUST_FUND_AMOUNT TEAM_CODE UTIL_DAILY UTIL_OBLIGO UTIL_OBLIGO_ADJ UTIL_OBLIGO_BUSINESS ; Run;

Ronein · ‎02-18-2025

Hello Lets say I have a big data set (25 million rows and 30 columns) in a permanent library and I want to select specific rows by criteria. I just used sashelp.class for example. From these 4 ways which way is best (Highest run speed) and which is worst(lowest run speed)? How would you rank these 4 methods by speed?(best to worst)? Is there any other way to do it? If I add Index then the 4 ways run more quickly? Note- in real life data set is much much bigger and sashelp.class is only an example proc sql; create table t1 as select * from sashelp.class where sex='F' ; quit; Data t2; set sashelp.class(Where=( sex='F')); Run; Data t3(Where=(sex='F')); set sashelp.class; Run; proc append data=sashelp.class(Where=( sex='F')) base=t4;quit;

Ronein · ‎02-17-2025

Hello I want to use proc sort of a data set located in WORK library. This data set has 29 million rows and 76 columns- 9 of them char with length 2014 and 67 numeric (length 8 It take almost 1 hour to sort it proc sort data=ACCOUNT_MONTHLY_DATA_RF_CS_12; by UPDATE_DATE FK_APPLICATION FK_MONTHLY_DATA_ACCOUNT REFERENCE_DATE; Run; Any idea what do to to reduce run time? Here is the LOG 1 The SAS System 10:05 Monday, February 17, 2025 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='V13_Run_Via_Macro_Added_Birth_Date_TO_YANIV.sas'; 4 %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTPATHHOST=''; 7 %LET _CLIENTPROJECTNAME=''; 8 %LET _SASPROGRAMFILE='M:\aתחום מודלים\מסגרות 8 ! סיכון\בקרות\בקרות_אצווה_פרטי_שימושי_עבור_רחל\V13_Run_Via_Macro_Added_Birth_Date_TO_YANIV.sas'; 9 %LET _SASPROGRAMFILEHOST='HKSU102561A79'; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=SVG; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 %macro HTML5AccessibleGraphSupported; 15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH; 16 %mend; 17 FILENAME EGHTML TEMP; 18 ODS HTML5(ID=EGHTML) FILE=EGHTML 19 OPTIONS(BITMAP_MODE='INLINE') 20 %HTML5AccessibleGraphSupported NOTE: The ACCESSIBLE_GRAPH option is pre-production for this release. 21 ENCODING='utf-8' 22 STYLE=HTMLBlue 23 NOGTITLE 24 NOGFOOTNOTE 25 GPATH=&sasworklocation 26 ; NOTE: Writing HTML5(EGHTML) Body file: EGHTML 27 28 proc sort data=ACCOUNT_MONTHLY_DATA_RF_CS_12; 29 by UPDATE_DATE FK_APPLICATION FK_MONTHLY_DATA_ACCOUNT REFERENCE_DATE; 30 Run; NOTE: There were 29334832 observations read from the data set WORK.ACCOUNT_MONTHLY_DATA_RF_CS_12. NOTE: SAS threaded sort was used. NOTE: The data set WORK.ACCOUNT_MONTHLY_DATA_RF_CS_12 has 29334832 observations and 75 variables. NOTE: Compressing data set WORK.ACCOUNT_MONTHLY_DATA_RF_CS_12 decreased size by 96.39 percent. Compressed is 151230 pages; un-compressed would require 4190691 pages. NOTE: PROCEDURE SORT used (Total process time): real time 38:44.79 user cpu time 8:39.11 system cpu time 4:22.12 memory 6325149.70k OS Memory 6348620.00k Timestamp 02/17/2025 11:28:44 AM Step Count 34 Switch Count 3298 Page Faults 0 Page Reclaims 4844240 Page Swaps 0 Voluntary Context Switches 9834164 Involuntary Context Switches 19362 Block Input Operations 0 Block Output Operations 0 31 32 2 The SAS System 10:05 Monday, February 17, 2025 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

Ronein · ‎02-12-2025

Hello I want to round the number by 500 (up or down) for example 17524.5 will be converted to 18000 25475.37 will be converted to 25000 6475.3 will be converted to 6000 what is the way to do it please?

Ronein · ‎02-09-2025

Hello What is the way to calculate percentage of X and of W for each group (group is defined by Z1). I want to calculate it via proc report and display the report via proc report Thanks Data have; input z1 $ Z2 x w; cards; A 1 10 20 A 2 20 30 A 3 40 50 B 1 15 30 B 2 25 30 B 3 40 50 ; run; proc sql; create table t1 as select sum(X) as TOTAL_X, sum(w) as TOTAL_w from have ; quit; data t2; set have ; if _n_=1 then set t1; PCT_X=X/TOTAL_X; PCT_W=W/TOTAL_w; format PCT_X PCT_W percent8.1; Run; proc report data=t2; run;

Online Status	Online
Date Last Visited	13m ago

Find client number

compress and upcase in Merge

send email +attach log file only in case of error or warning

Read Log

calculate seq number within group

check if string contain substring.

Re: apply labels of columns from other data set

apply labels of columns from other data set

wide or long structure

Re: order of data sets that appear in window

Re: calculate seq number within group

Re: wide or long structure

Re: wide or long structure

Re: wide or long structure

Re: wide or long structure

Full Merge

Re: Max per loan in last 7 days

Re: Max per loan in last 7 days

Max per loan in last 7 days

Long to wide

Proc SQL into (MACRO)

color cells in proc report if value greater than fix value

check failure in follow up period

Number of missing Char values in row

Re: number missing values in each obs (Numeric)

Re: number missing values in each obs (Numeric)

number missing values in each obs (Numeric)

macro Check_DataSet_Exist

check if data set has Index

Re: SET data set-Run speed

Re: Ling run time for proc sort

SET data set-Run speed

Ling run time for proc sort

Round by 500

PROC REPORT calculate percent

SAS Users Group in Israel