It looks like people have been overwhelmed by the wall of text, and have no time to spend deciphering your complex explanations.  Try simplifying what you want to achieve to increase uptake. ... View more

Thanks everyone. All the solution here works. I wish i could mark all the answers as solution. ... View more

Here is an example: id x 1 1 1 2 2 2 2 3 2 4 if using my code with firstobs=2 option would look forward an obs as Kurt pointed out. -----> id x _id _x <--- is from the second dataset with firstobs=2 1 1 1 2 1 2 2 2 2 2 2 3 2 3 2 4 2 4 . . ... View more

The data step creating have has errors: unclosed quotes and a run in the middle of the step. The format statement is missing, too.   Saving the first values of police_effective_date and police_end_date in retained variables and using them to set flag when the data step reached to last obs for police seem to create what you want: data want; set have; by police; length flag $ 10 first_eff_date first_end_date 8 ; retain first_:; if first.police then do; first_eff_date = police_effective_date; first_end_date = police_end_date; end; if last.police then do; if police_end_date > first_end_date then do; if police_effective_date = first_eff_date then flag = "EXTENDED"; if police_effective_date > first_eff_date then flag = "SHIFTED"; end; end; drop first_:; run; Assuming the data is sorted by police and end. ... View more

Check the MERGE skill proposed by Me and Arthur.T : https://support.sas.com/resources/papers/proceedings15/2785-2015.pdf   data have; input office $ dept $ year seq $ code $ me_amt me_pct; datalines; LA IT 2024 1 101 10000 0 LA IT 2024 1 102 66 12 MI FIN 2024 1 101 333 1 MI FIN 2024 1 102 98.7 12.3 ; run; proc sql noprint; select distinct catt('have(where=(code="',code,'" ) rename=(me_amt=me_amt_',code,' me_pct=me_pct_',code,'))') into :merge separated by ' ' from have; quit; data want; merge &merge.; by office dept year; drop code; run;     ... View more

So just remove the BY group processing. data have; input trx_date :date. amount c_type $ 15-30 ; format trx_date date9.; cards; 01Jan2024 100 1 01Jan2024 200 2 01Jan2024 300 3 01Jan2024 200 1 or 3 03Feb2024 1 2 03Feb2024 1.5 3 03Feb2024 2 1 or 2 or 3 31Mar2024 888 1 31Mar2024 1088 2 ; You can just treat any value that cannot be converted to a number as one to trigger looking back for the "highest" previous value.  You could use my previous array method. data want ; set have; array found found1-found3 ; retain found1-found3; n_type = input(c_type,??32.); if n_type in (1:3) then found[n_type]=1; else do n_type=1,3,2; if found[n_type] then leave; end; if n_type > 3 then n_type=.; run; Or just keep a single "highest" value variable to use. data want2 ; set have; retain highest ; array order [3] _temporary_ (2 3 1); n_type = input(c_type,??32.); if n_type=1 then highest=1; else if n_type=3 and (highest ^= 1) then highest=3; else if n_type=2 and (highest=.) then highest=2; else n_type=coalesce(n_type,highest); run; Your example is probably not the best for testing.  Since the first real value is 1 that means any missing values are going to be replaced by 1.  To see if the strange ordering works you need different test data. ... View more

Untested code. Something like this should work. proc sql; select sum(AMT * (YEAR=2023)) - sum(AMT * (YEAR=2024)) as DIFF from MOCKUP; quit; The amount is nullified when the year test is not true.   ... View more

@StickyRoll wrote: Hi @PaigeMiller  Thanks for the Proc Freq suggestion. It works. However, may i find out on the "var" part? mad_age is from the source but is not used for the computation. The variable that I am looking at is actually "MAD_DRVEXPYR1" instead. Do i just substitute it with "MAD_DRVEXPYR1" for VAR? I am just counting rows, so it doesn't matter which numeric variable I choose to do the counting of rows. MAD_AGE would give the same answer for this data set as any other numeric variable. (Assuming there are no missing values) ... View more

I don't have Viya at my fingertips, but given the documentation there is a Sub-flow step, given that you have at least SAS Studio Analyst License: SAS Help Center: About Subflows   If you are having many "levels" of flows, you need to use Job flows in Environment Manager (since Sub-flows can't contain other Sub-flows): Tips for Scheduling Flows with SAS Viya Environment Manager - SAS Users   ... View more

And you can avoid any macro coding altogether: where closingcycle = " " !! substr("&_CYCLE.",1,6); Which makes it clearer that you do want two leading blanks. ... View more

Can you share me your base sas code to extract direct/indirect roles from smc Suppose userA is the of groupA, roleZ, groupC and groupA is part of roleP, groupD groupD is part of groupF groupF is part of roleM groupC is part of roleX Final output should be userA roleZ userA roleP userA roleM userA roleX I can help with your original question as well today, I think rgf roles should be in some postgres table, just need to figure it out ... View more

There is usually no need to create so many macro variables.    Just use one macro variable to hold the current word from the list. %let carbrand = bmw ford lincoln honda toyota nissan; %do index=1 %to %sysfunc(countw(&carbrand,%str( ))); %let car=%scan(&carbrand,&index,%str( )); %* code that uses &CAR for something ; %end;   ... View more

My bad (posted before sufficient amount of caffeine). No date literal needed: %let rpt_date2 = %sysfunc(inputn(&rpt_date.,date9.),yymmddn8.); See this log: 69 %let rpt_date = 31dec2019; 70 71 %let rpt_date2 = %sysfunc(inputn(&rpt_date.,date9.),yymmddn8.); 72 73 %put &=rpt_date2.; RPT_DATE2=20191231 ... View more

You must use the datatype specific version of the PUT function PUTC/PUTN with %SYSFUNC.  See documentation for others.   34 %let rpt_date="30Nov2021"d; 35 %let rpt_date2=%sysfunc(putn(&rpt_date.,yymmddn8.)); 36 %put NOTE: &=rpt_date2; NOTE: RPT_DATE2=20211130 ... View more