About Ronein

Ronein · ‎01-14-2025

Hello For each customer I have 12 months follow up with indicator of failure (1/0). I want to know when is the last month (closest month to today: today is month index 12) that customer enter to failue. For example: Customer 111: month 9 Customer 222: month 11 Customer 333: Null Since today not in failure). Customer 444: month 1 since he was always in failure) Pay attention that if customer not in failure today then get value null. What is the way to calculate it please? Data have; input CustID monindex failureInd; cards; 111 1 0 111 2 0 111 3 1 111 4 1 111 5 0 111 6 0 111 7 1 111 8 0 111 9 1 111 10 1 111 11 1 111 12 1 222 1 0 222 2 0 222 3 0 222 4 0 222 5 0 222 6 0 222 7 0 222 8 0 222 9 0 222 10 0 222 11 1 222 12 1 333 1 0 333 2 0 333 3 0 333 4 0 333 5 0 333 6 0 333 7 0 333 8 0 333 9 1 333 10 1 333 11 0 333 12 0 444 1 1 444 2 1 444 3 1 444 4 1 444 5 1 444 6 1 444 7 1 444 8 1 444 9 1 444 10 1 444 11 1 444 12 1 ; Run;

Ronein · ‎01-10-2025

Hello Lets say I have a data set with many rows and many columns and I want to select the rows of one specific customer. What is the most quick way to do it between these 4 ways or maybe there is a better way? Data Way1; set r_r.t100millionrows(Where=(CustID=123456)); Run; proc sql; create table Way2 as select * from r_r.t100millionrows where lakoach=7550 ; quit; proc sql; create table Way3 as select * from r_r.t100millionrows(Where=(lakoach=7550)) ; quit; proc sql; create table Way4(Where=(lakoach=7550)) as select * from r_r.t100millionrows ; quit;

Ronein · ‎01-10-2025

Hello I try to create this format. What is the way to create it by dynamic code and not manually type all this code? proc format; value F_Ronein 0='(1) 0' 0<-100='(2) 0-100' 100<-200='(3) 100-200' 200<-300='(4) 200-300' 300<-400='(5) 300-400' 400<-500='(6) 400-500' 500<-1000='(7) 500-1000' 1000<-2000='(8) 1000-2000' 2000<-3000='(9) 2000-3000' 3000<-4000='(10) 3000-4000' 4000<-5000='(11) 4000-5000' 5000<-6000='(12) 5000-6000' 6000<-7000='(13) 6000-7000' 7000<-8000='(14) 7000-8000' 8000<-9000='(15) 8000-9000' 9000<-10000='(16) 9000-10000' 10000<-15000='(17) 10000-15000' 15000<-High='(18) 15000+' ; Run;

Ronein · ‎01-09-2025

Hello I have a data set with following columns: Group ID individual ID month YYYYMM so for a specific Group ID can have multiple individual IDS. My task- I need to identify if there is a change over time in the family structure. For example: Family 123456: in 202401 family structure is 111,222,333 and in 202402 111,222,444 so since it is not same then Indincator=1 What is the way to calculate it? Why my code provide wrong result? Data have; Input month GroupID IndividualID; GROUP_Id_MONTH=catx('-',GroupID,month); cards; 202401 123456 111 202401 123456 222 202401 123456 333 202402 123456 111 202402 123456 222 202402 123456 444 202401 135798 555 202401 135798 666 202402 135798 555 202402 135798 666 ; Run; /*Data Want;*/ /*input GroupID Ind_Change;*/ /*cards;*/ /*123456 1*/ /*135798 0*/ /*;*/ /*Run;*/ PROC SORT DATA=have ;BY GROUP_Id_MONTH;rUN; data _WANT_; length cat $20.; do until (last.GROUP_Id_MONTH); set have; by GROUP_Id_MONTH notsorted; cat=catx(',',cat,IndividualID); end; drop IndividualID; lag_cat=LAG1(cat); if FIRST.GROUP_Id_MONTH THEN DO; lag_cat=.; Ind_Change=.; end; else do; IF lag_cat ne cat then Ind_Change=1; end; retain Ind_Change; Run;

Ronein · ‎01-08-2025

Thanks Indeed dates in my data set are sas dates. So as I see I has 2 mistakes. 1- forgot 'd in the format definition 2- used information instead of format statement

Ronein · ‎01-08-2025

Please look at customer 5 data have; input CustID mon Ind; cards; 111 1 0 111 2 0 111 3 1 111 4 0 111 5 0 111 6 0 111 7 0 111 8 0 111 9 1 111 10 0 111 11 0 111 12 0 222 1 0 222 2 0 222 3 0 222 4 0 222 5 0 222 6 0 222 7 0 222 8 0 222 9 0 222 10 0 222 11 0 222 12 0 333 1 0 333 2 0 333 3 0 444 1 1 444 2 0 444 3 0 444 4 0 444 5 0 444 6 0 444 7 0 444 8 0 444 9 1 444 10 0 444 11 0 444 12 1 555 1 0 555 2 1 555 3 1 555 4 1 555 5 1 555 6 1 555 7 1 555 8 1 555 9 1 555 10 1 555 11 1 555 12 0 ; Run; data want; input CustID mon Ind serial; cards; 111 1 0 1 111 2 0 1 111 3 1 2 111 4 0 2 111 5 0 2 111 6 0 2 111 7 0 2 111 8 0 2 111 9 1 3 111 10 0 3 111 11 0 3 111 12 0 3 222 1 0 1 222 2 0 1 222 3 0 1 222 4 0 1 222 5 0 1 222 6 0 1 222 7 0 1 222 8 0 1 222 9 0 1 222 10 0 1 222 11 0 1 222 12 0 1 333 1 0 1 333 2 1 2 333 3 0 2 444 1 1 1 444 2 0 1 444 3 0 1 444 4 0 1 444 5 0 1 444 6 0 1 444 7 0 1 444 8 0 1 444 9 1 2 444 10 0 2 444 11 0 2 444 12 1 3 555 1 0 1 555 2 1 2 555 3 1 3 555 4 1 4 555 5 1 5 555 6 1 6 555 7 1 7 555 8 1 8 555 9 1 9 555 10 1 10 555 11 1 11 555 12 0 11 ; Run;

Ronein · ‎01-08-2025

No good, Look at customer 4 that I added now. He should start from serial 1 and not 2 data have; input CustID mon Ind; cards; 111 1 0 111 2 0 111 3 1 111 4 0 111 5 0 111 6 0 111 7 0 111 8 0 111 9 1 111 10 0 111 11 0 111 12 0 222 1 0 222 2 0 222 3 0 222 4 0 222 5 0 222 6 0 222 7 0 222 8 0 222 9 0 222 10 0 222 11 0 222 12 0 333 1 0 333 2 0 333 3 0 444 1 1 444 2 0 444 3 0 444 4 0 444 5 0 444 6 0 444 7 0 444 8 0 444 9 1 444 10 0 444 11 0 444 12 1 ; Run; data want; input CustID mon Ind serial; cards; 111 1 0 1 111 2 0 1 111 3 1 2 111 4 0 2 111 5 0 2 111 6 0 2 111 7 0 2 111 8 0 2 111 9 1 3 111 10 0 3 111 11 0 3 111 12 0 3 222 1 0 1 222 2 0 1 222 3 0 1 222 4 0 1 222 5 0 1 222 6 0 1 222 7 0 1 222 8 0 1 222 9 0 1 222 10 0 1 222 11 0 1 222 12 0 1 333 1 0 1 333 2 1 2 333 3 0 2 444 1 1 1 444 2 0 1 444 3 0 1 444 4 0 1 444 5 0 1 444 6 0 1 444 7 0 1 444 8 0 1 444 9 1 2 444 10 0 2 444 11 0 2 444 12 1 3 ; Run;

Ronein · ‎01-07-2025

I have tried but it didnt work data have; format ddate ddmmyy10.; input ddate : date9.; cards; 01JAN1900 01JAN1900 15DEC2023 16JAN2024 07SEP2021 ; Run; proc format; invalue mydate '01JAN1900'='n/a'; other=[date9.]; run; data want; set have; format ddate mydate.; Run;

Ronein · ‎01-07-2025

It is not working, I want to apply it on have data set but do it when create want data set (I dont want to do it in the data step with cards statement because in real word I dont create the data set with dates and only get it as external data set) data have; format ddate ddmmyy10.; input ddate : date9.; cards; 01JAN1900 01JAN1900 15DEC2023 16JAN2024 07SEP2021 ; Run; proc format; invalue mydate '01JAN1900'=. other=[date9.]; run; data want; set have; informat ddate mydate.; Run;

Ronein · ‎01-07-2025

Hello I want to add format to ddate field in the following way: IF the date is 01/01/1900 then it would show " Date not exist" and in other cases will show the date So; 01JAN1900 --will show Date not exist 01JAN1900 --will show Date not exist 15DEC2023 --will show 15/12/2023 16JAN2024--will show 16/01/2024 07SEP2021--will show 07/09/2021 what is the way to do it please? data have; format ddate ddmmyy10.; input ddate : date9.; cards; 01JAN1900 01JAN1900 15DEC2023 16JAN2024 07SEP2021 ; Run;

Ronein · ‎01-07-2025

Hello for each customer there are until 12 rows (each row represent account balance in end of month). There is a column called Ind that get value 1 or 0 (1 means that there is a change in contract number in this month). I want to create a new varaible caled serial that create a serial numbers for each customerID by the following rule: If there is a change (means that Ind=1) then go up by one in the serial number until there is another value 1 in field Ind. what is the way to do it please? I also show the want data set data have; input CustID mon Ind; cards; 111 1 0 111 2 0 111 3 1 111 4 0 111 5 0 111 6 0 111 7 0 111 8 0 111 9 1 111 10 0 111 11 0 111 12 0 222 1 0 222 2 0 222 3 0 222 4 0 222 5 0 222 6 0 222 7 0 222 8 0 222 9 0 222 10 0 222 11 0 222 12 0 333 1 0 333 2 0 333 3 0 ; Run; data want; input CustID mon Ind serial; cards; 111 1 0 1 111 2 0 1 111 3 1 2 111 4 0 2 111 5 0 2 111 6 0 2 111 7 0 2 111 8 0 2 111 9 1 3 111 10 0 3 111 11 0 3 111 12 0 3 222 1 0 1 222 2 0 1 222 3 0 1 222 4 0 1 222 5 0 1 222 6 0 1 222 7 0 1 222 8 0 1 222 9 0 1 222 10 0 1 222 11 0 1 222 12 0 1 333 1 0 1 333 2 1 2 333 3 0 2 ; Run;

Ronein · ‎01-07-2025

Hello Why is wrong that these 2 data sets are not created well? Data have1; infile cards; input CustID date date9. Ind_Change balance; format date date9.; cards; 111 01JAN2025 0 111 02JAN2025 0 111 03JAN2025 0 111 04JAN2025 1 111 05JAN2025 0 111 06JAN2025 0 111 07JAN2025 0 111 08JAN2025 0 111 09JAN2025 1 111 10JAN2025 0 ; Run; Data have2; infile datalines dlm="," dsd; input CustID date date9. Ind_Change balance; format date date9.; cards; 111,01JAN2025,0,50 111,02JAN2025,0,80 111,03JAN2025,0,10 111,04JAN2025,1,15 111,05JAN2025,0,30 111,06JAN2025,0,80 111,07JAN2025,0,90 111,08JAN2025,0,140 111,09JAN2025,1,130 111,10JAN2025,0,125 ; Run;

Ronein · ‎01-07-2025

Sorry but this is not the final solution, need one more transpose to get in it full wide structure

Ronein · ‎01-05-2025

you are right. IT is because I forgot add prefix. Data have; input CustID YYYYMM monIndex X W Z R Q Y ; cards; 111 202401 1 10 11 13 15 19 1 111 202402 2 17 17 12 10 18 0 111 202403 3 19 18 12 12 11 0 111 202404 4 12 12 12 13 14 1 111 202405 5 16 15 14 18 17 1 111 202406 6 16 17 17 18 15 0 222 202401 1 11 11 11 11 11 0 222 202402 2 12 13 14 11 16 0 222 202403 3 19 19 18 16 19 1 222 202404 4 17 17 16 15 18 1 222 202405 5 16 15 16 16 17 0 222 202406 6 14 16 14 13 17 0 ; Run /**Task: Convert Long to Wide*****/ /***WAY1***/ proc transpose data=have out=wide_1 prefix=X; by CustID ; ID monIndex; Var X; Run; proc transpose data=have out=wide_2 prefix=W; by CustID ; ID monIndex ; Var W; Run; proc transpose data=have out=wide_3 prefix=Z; by CustID ; ID monIndex ; Var Z; Run; proc transpose data=have out=wide_4 prefix=R; by CustID ; ID monIndex ; Var R; Run; proc transpose data=have out=wide_5 prefix=Q; by CustID ; ID monIndex ; Var Q; Run; proc transpose data=have out=wide_6 prefix=Y; by CustID ; ID monIndex ; Var Y; Run; Data want; Merge wide_1-wide_6; by CustID; DROP _name_; Run;

Ronein · ‎01-05-2025

Thanks for all, I summarize the provided solutions Data have; input CustID YYYYMM monIndex X W Z R Q Y ; cards; 111 202401 1 10 11 13 15 19 1 111 202402 2 17 17 12 10 18 0 111 202403 3 19 18 12 12 11 0 111 202404 4 12 12 12 13 14 1 111 202405 5 16 15 14 18 17 1 111 202406 6 16 17 17 18 15 0 222 202401 1 11 11 11 11 11 0 222 202402 2 12 13 14 11 16 0 222 202403 3 19 19 18 16 19 1 222 202404 4 17 17 16 15 18 1 222 202405 5 16 15 16 16 17 0 222 202406 6 14 16 14 13 17 0 ; Run /**Task: Convert Long to Wide*****/ /***WAY1***/ proc transpose data=have out=wide_1 prefix=X; by CustID ; ID monIndex; Var X; Run; proc transpose data=have out=wide_2 prefix=W; by CustID ; ID monIndex ; Var W; Run; proc transpose data=have out=wide_3 prefix=Z; by CustID ; ID monIndex ; Var Z; Run; proc transpose data=have out=wide_4 prefix=R; by CustID ; ID monIndex ; Var R; Run; proc transpose data=have out=wide_5 prefix=Q; by CustID ; ID monIndex ; Var Q; Run; proc transpose data=have out=wide_6 prefix=Y; by CustID ; ID monIndex ; Var Y; Run; Data want; Merge wide_1-wide_6; by CustID; DROP _name_; Run; /***WAY2-Quick way--VAR statement accepts multiple variables, no need to transpose each variable separately***/ proc transpose data=have out=want prefix=X; by CustID ; ID monIndex; Var X W Z R Q Y; /*Var X--Y;*/ DROP _name_; Run; /***WAY3-Double Transpose solution***/ proc transpose data=have out=Longer; by custid yyyymm monIndex; var x -- y ; run; proc sort data=Longer; by custid _name_ monindex; run; /*If you want the new variables ordered differently add a PROC SORT step in between*/ proc transpose data=Longer out=want(drop=_name_); by custid ; id _name_ monindex; var col1 ; run; /***WAY4-IF you have very big data set****/ data want; Merge have(where=(monIndex=1) rename=(X=X1 W=W1 Z=Z1 R=R1 Q=Q1 Y=Y1)) have(where=(monIndex=2) rename=(X=X2 W=W2 Z=Z2 R=R2 Q=Q2 Y=Y2)) have(where=(monIndex=3) rename=(X=X3 W=W3 Z=Z3 R=R3 Q=Q3 Y=Y3)) have(where=(monIndex=4) rename=(X=X4 W=W4 Z=Z4 R=R4 Q=Q4 Y=Y4)) have(where=(monIndex=5) rename=(X=X5 W=W5 Z=Z5 R=R5 Q=Q5 Y=Y5)) have(where=(monIndex=6) rename=(X=X6 W=W6 Z=Z6 R=R6 Q=Q6 Y=Y6)) ; by CustId; drop YYYYMM monIndex; Run; /***WAY4-IF you have very big data set--Using macro var tooo****/ proc sql noprint; select distinct catt('have(where=(monIndex=',monIndex,') rename=(X=X',monIndex,' W=W',monIndex,' Z=Z',monIndex,' R=R',monIndex,' Q=Q',monIndex,' Y=Y',monIndex,'))') into :merge separated by ' ' from have ; quit; %put &merge; data want; merge &merge.; by CustId; drop YYYYMM monIndex; run; /**Way5-If you don't have gap in monIndex variable and dataset has been sorted by CustID and monIndex, Could try proc summary solution***/ proc sql noprint; select max(n) into :n From (select count(*) as n from have group by CustID); quit; %put &n; proc summary data=have nway; class CustID; output out=want(drop=_type_ _freq_) idgroup(out[&n] (X W Z R Q Y)=); run;

Online Status	Offline
Date Last Visited	yesterday

Re: create seq number for each ownership structure

Re: create seq number for each ownership structure

create seq number for each ownership structure

Re: Find client number

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: 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

Find last month customer enter failure in Long structure data

Select one customer

proc format Ronein

change over rows

Re: proc format: with conditions

Re: create serial number by CustID with related to IND column

Re: create serial number by CustID with related to IND column

Re: proc format: with conditions

Re: proc format: with conditions

proc format: with conditions

create serial number by CustID with related to IND column

create data set using card with dates values

Re: long to wide for multiple varaibles

Re: long to wide for multiple varaibles

Re: long to wide for multiple varaibles

SAS Users Group in Israel