About Swapnil_21

Matthew_Galati · ‎03-31-2022

Here is an approach that uses Viya's OPTNETWORK to solve it. This uses the same algorithms that @RobPratt shows through the OPTMODEL interface. It finds the connected components and then runs cycle detection by group (where group = connected component). For large scale this approach would scale nicely as it does all the heavy work on the server (either shared or distributed memory). /* Find weakly connected components (ignore singletons) */ proc optnetwork links = sascas1.have(where=(col2 ne '')) outLinks = sascas1.ConCompLinks; connectedComponents; linksVar from=col1 to=col2; run; /* Determine if the component is directed acyclic. */ proc optnetwork direction = directed links = sascas1.ConCompLinks; linksVar from=col1 to=col2; cycle; displayout SolutionSummary = SolutionSummary; by concomp; run; /* For each component that is not acyclic, its nodes are NotOk (and append singletons as Ok). */ proc sql; create table tmp as select a.*, b.numCycles as hasCycle from sascas1.ConCompLinks as a left join sascas1.SolutionSummary as b on a.concomp = b.concomp; quit; data want; set tmp sascas1.have(where=(col2 eq '')); if(hasCycle > 0) then flag="NotOk"; else flag="Ok"; run; proc print data=want; run;

Kurt_Bremser · ‎03-22-2022

See this code: data have; infile datalines delimiter=","; input Flow_Name: $256. Job_Name: $256. Dependency: $1024.; cards; CE_B3_STACK,90-IDM-STK_FMTS-00001, CE_B3_STACK,91-IDM-STK_FW-00001, CE_B3_STACK,92-IDM-STK_PRTY-00001,91-IDM-STK_FW-00001 CE_B3_STACK,93-IDM-STK_PRXY-00001,91-IDM-STK_FW-00001|92-IDM-STK_PRTY-00001 CE_B3_STACK,94-IDM-STK_B3_ASSET_CLASS-00001,90-IDM-STK_FMTS-00001|92-IDM-STK_PRTY-00001|93-IDM-STK_PRXY-00001 CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00001, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00002, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00003, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00004, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00005, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00006, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00007, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00008, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00009, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00010, CE_BSE_CUST_SGB,20-BSE-CUST_SGB-00001,20-BSE-CUST_SGB-00001|20-BSE-CUST_SGB-00002|20-BSE-CUST_SGB-00003|20-BSE-CUST_SGB-00004|20-BSE-CUST_SGB-00005|20-BSE-CUST_SGB-00006|20-BSE-CUST_SGB-00007|20-BSE-CUST_SGB-00008 CE_BSE_CUST_SGB,21-BSE-CUST_SGB-00001,20-BSE-CUST_SGB-00001|20-BSE-CUST_SGB-00002|20-BSE-CUST_SGB-00003|20-BSE-CUST_SGB-00004|20-BSE-CUST_SGB-00005|20-BSE-CUST_SGB-00006|20-BSE-CUST_SGB-00007|20-BSE-CUST_SGB-00008|XXX ; /* find duplicate job_names */ proc sort data=have out=dedup /* so we do not destroy "have" */ dupout=dups (keep=flow_name job_name) nodupkey ; by flow_name job_name; run; /* find missing job_names and recursive definitions */ data recursive_miss (keep=flow_name flag); set have; by flow_name; if _n_ = 1 then do; declare hash f (); f.definekey("job_name"); f.definedone(); end; if first.flow_name then rc = f.clear(); rc = f.add(); if findw(dependency,job_name,"|","t") then do; flag = "recursive"; output; end; else do i = 1 to countw(dependency,"|","t"); if f.find(key:scan(dependency,i,"|")) ne 0 then do; flag = "missing"; output; leave; end; end; run; data n_ok (keep=flow_name); set dups recursive_miss ; by flow_name; if first.flow_name; run; data want; merge have n_ok (in=n) ; by flow_name; flag = ifc(n,"not ok","ok"); run; I corrected the last dependency from your example data so that a duplicate and recursive job_name is actually created, and then added an additional observation to create a "missing" job.

Patrick · ‎03-03-2022

@Swapnil_21 Given the column names are you implementing within one of the Risk Stratum solutions? If so then I've created lately a macro that does what you're after. Happy to share if you provide a bit more detail.

ErikLund_Jensen · ‎02-27-2022

Hi @Swapnil_21 If you just want to mask the string, you could translate it to octagonal representation. The resulting string is printable and contains only digits 0-7. Everybody could unmask the string if they figure out that it is actually octagonal, so it is not a real encryption with a key, but who would think of octagonal codes in 2022? And it is a very simple operation i SAS: data a; text = 'my name is john'; text_masked = put(text,$octal200.); text_restored = input(text_masked,$octal200.); put text / text_masked / text_restored; run; my name is john 155171040156141155145040151163040152157150156 my name is john turn the string and still have it as a string of printable caharactersby turning it as a string of digits, so it

andreas_lds · ‎02-21-2022

Unfortunately you didn't provide data in usable form, so i used a modified version sashelp.class. The subsequent steps extract the information of interest. data work.class; set sashelp.class; length _m_Age _m_Weight 8; _m_Age = Age + 10; _m_Weight = round(Weight * 1.5, 0.01); run; data work.Variables; set sashelp.vcolumn(where=(libname = 'WORK' and MemName = 'CLASS' and not ('_m_' =: Name))); bob = _n_; keep Name bob; run; data work.M_Variables; set sashelp.vcolumn(where=(libname = 'WORK' and MemName = 'CLASS' and '_m_' =: Name)); length mVar $ 32; mVar = Name; Name = substr(Name, 4); keep Name mVar; run; The first solution will not change your dataset, but will have a higher runtime, if the dataset is large. data _null_; set work.Variables end= jobDone; if _n_ = 1 then do; if 0 then set work.M_Variables; declare hash h(dataset: 'work.M_Variables'); h.defineKey('Name'); h.defineData('mVar'); h.defineDone(); call execute('proc sql noprint; create table work.fixed as select'); end; mVar = ' '; if _n_ > 1 then do; call execute(','); end; if h.find() = 0 then do; call execute(catx(' ', mVar, 'as', Name)); end; else do; call execute(Name); end; if jobDone then do; call execute(' from work.class; quit;'); end; run; And here's another solution modifying your dataset, because there is no undo, you should be 100% sure, that you understand what happens. data _null_; set work.Variables end= jobDone; if _n_ = 1 then do; if 0 then set work.M_Variables; declare hash h(dataset: 'work.M_Variables'); h.defineKey('Name'); h.defineData('mVar'); h.defineDone(); end; length dropList $ 6600 /* 200 Variables, max length 32 chars, +1 to hold the separator */ renameList $ 14000 /* 66 chars for each pair oldname/newname incl. separators */ ; retain dropList renameList; if h.find() = 0 then do; dropList = catx(',', dropList, Name); renameList = catx(' ', renameList, cats(mVar, '=', Name)); end; if jobDone and not missing(dropList) then do; call execute(catx(' ', 'proc sql noprint; alter table work.Class drop', dropList, '; quit;')); call execute(catx(' ', 'proc datasets nolist library=work; modify Class; rename', renameList, ';quit;')); end; run;

Ksharp · ‎02-17-2022

As Kurt said. rc1 = h.find(key:Account_ID); if rc1 = 0 then account_id = masked_value; rc2 = h.find(key:customer_id); if rc2 = 0 then customer_id = masked_value; run;

Ksharp · ‎02-12-2022

data want; set have(keep=ACCOUNT_ID rename=(ACCOUNT_ID = LookUp)) have(keep=CUSTOMER_ID rename=(CUSTOMER_ID = LookUp)) have(keep=APPLICATION_ID rename=(APPLICATION_ID = LookUp)) ; run;

Oligolas · ‎11-09-2021

data have; input var1-var3; cards; 10 20 30 40 50 60 ; run; data Appended_table; input var2 var4; cards; 100 200 300 400 ; run; data Appended_table2; set Appended_table have(in=a) ; if a then var4=var1*2;*Did U mean var4=var2*2 ?; keep var2 var4; run; proc print;run;

ballardw · ‎11-03-2021

So you want to create a random number. Do you have any rules for how to make such? SAS has several functions that create random numbers and one, RAND that will create random numbers from many different distributions. Example of just a few: data example; do i=1 to 10; rn = rand('normal',0,1); rb = rand('binomial', .5,10); rbeta = rand('beta',4,12); rchi = rand('chisquare',17); output; end; run; You would call one of these, or other functions, to create your random number in a data step.

SASKiwi · ‎11-03-2021

Please post your program attempt. It's hard to help if we don't know what you have tried.

Shmuel · ‎08-09-2020

Comparing your wanted results to the input data, there is no any UPDATE of data, but just keeping the last row of a group. Check results of next code: data want; set have; by Original_A_GSTIN Original_A_RET_PERIOD Original_A_B2BI_CTIN Original_A_B2BI_IDT; if last.Original_A_B2BI_IDT; run;

r_behata · ‎11-13-2019

Data logic; input Sr_No Logic $2-100; infile datalines4 truncover; datalines4; 1 If rate=10 and principal_amt=100000 then emi=5000 2 If rate=9 and principal_amt=100000 then emi=4800 3 If income>1000000 then risk=10 4 If income<20000 then risk=80 5 if category='New Customer' then do; rate=10; risk=20; end; ;;;; run; data _null_; set logic end=eof; if _n_=1 then do; call execute('data want; set source;'); end; call execute(' ' !! catx(' ',Logic,';') !! ' '); if eof then do; call execute('run;'); end; run;

s_lassen · ‎11-04-2019

Instead of using append, you could use a data step to add the values: data oralib.target; modify oralib.target monthly_source; by Key Account_Num Reporting_Date Year; if _iorc_=%sysrc(_DSENMR) then do; /* key not found in master data */ _error_=0; output; end; run; As the Oracle database is checking the key values anyway (because of the unique key restraint), this may be just as fast as PROC APPEND.

hashman · ‎10-03-2019

@Swapnil_21: First, tell whomever has designed this "data structure" and intends to keep and maintain it this way: "Welcome to data processing and maintenance nightmare". Having said that, you can do what you need this way: data have ; input Cid Account_open_date :date. opening_Balance_apr19 Balance_apr19 Opening_balance_may19 Balance_may19 Opening_balance_jun19 Balance_jun19 Opening_balance_jul19 Balance_jul19 Opening_balance_aug19 Balance_aug19 ; format Account_open_date yymmdd10. ; cards ; 1 08-May-19 . . 300 400 100 200 100 150 170 80 2 13-Jun-19 . . . . 100 100 100 40 100 60 2 16-Jul-19 . . . . . 150 100 100 100 100 ; run ; data want (drop = _:) ; set have ; array nn _numeric_ ; do over nn ; if cmiss (nn) then continue ; _open = lowcase (vname (nn)) =: "opening_balance" ; _seq = sum (_seq, _open) ; if _seq = 1 or (_seq and not _open) then Required_Sum = sum (required_sum, nn) ; end ; run ; Kind regards Paul D.

ballardw · ‎09-30-2019

@Swapnil_21 wrote: Hi, I need to divide current month in 3 parts of days. 1-10 in part1 11-20 in part2 21-last day of month in part3. So there are 6 macro variables to be created like 01SEP2019:00:00:00 10SEP2019:00:00:00 11SEP2019:00:00:00 20SEP2019:00:00:00 21SEP2019:00:00:00 30SEP2019:00:00:00 I have managed to create first and last day of the month as below. But somehow unable to create remaining variables. Below is the code I have tried. Thanks in advance DATA _NULL_; CALL SYMPUT('a',CATS(put(INTNX("MONTH",today()-2,0,"B"),DATE9.),":00:00:00")); CALL SYMPUT('b',CATS(put(INTNX("MONTH",today()-2,0,"E"),DATE9.),":00:00:00")); CALL SYMPUT ('ORA_PREV_MON_E',CATS(put(INTNX("MONTH",today(),-1,"E"),DATE9.),":00:00:00")); /*CALL SYMPUT ("ORA_CURR_MON",cat("'",PUT(MONTH,date9.),"'"));;*/ RUN; %put &a &b &ORA_PREV_MON_E; You may strongly want to consider 1) making these DATE values if the time is always 00:00:00 and 2) looking at the TENDAY interval option for the INTNX and INTCK functions: I use October in the following example to demonstrate what happens in months with 31 days. data example; do day = 1,9,10,11,19,20,21,29,30,31; date= mdy(10,day,2019); date2 = intnx('tenday',date,0,'b'); format date date2 date9.; output; end; run;

Online Status	Offline
Date Last Visited	‎04-21-2022 01:12 AM

Re: Transitive Dependency Finder using code

Re: Transitive Dependency Finder using code

Re: Transitive Dependency Finder using code

Re: Transitive Dependency Finder using code

Transitive Dependency Finder using code

Re: Finding common value for a group

Finding common value for a group

Re: How to retain indexes after using set statement

How to retain indexes after using set statement

Re: Replace individual characters

How to retain indexes after using set statement

Re: Alternative to proc sql update

Re: Transitive Dependency Finder using code

Re: Finding common value for a group

Re: How to retain indexes after using set statement

Re: Replace individual characters

Re: Complex SQL rename query

Re: Alternative to proc sql update

Re: Append few columns in another table

Re: Write newly created variable to another dataset

Re: Random number store in other table

Re: Column name, table name in another table

Re: Update values in same table based on most recent values

Re: Execute logic based on variable from table

Re: FInding value present before appending

Re: Sum based on column name and number

Re: Divide month into 3 parts in timestamp