I agree with @s_lassen 's code showing there is no need for a macro.   One minor suggestion that could greatly improve efficiency if the dataset is large.  Generate ALL as a dataset VIEW rather than as a dataset FILE.  This will allow the two passthroughs of HAVE to be synchronized rather than one at a time.   {added comment).  As a result there will be no need to write the intermediate dataset ALL to disk, just to immediately reread it.   data ALL / view=ALL; ... data WANT; ...   ... View more

@Quentin wrote:   With companies storing more and more files in Office 365 cloud stuff (SharePoint/Teams/OneDrive/PowerBI...) I feel like MS is in effect building a mote around those files, making it easy to work on them from within their ecosystem of tools, but less easy to get them out (without using API's).   mote ("A tiny particle, speck, or piece of dust").  I think you meant "moat": ("A broad deep ditch that is dug around a castle or other fortress as a defense against attach").   I can see justification for either.   Enough already - I'll go back to my day job now. ... View more

I liked @A_Kh's suggestion to sort by USUBJID descending ACTARM, which is intended to put missing ACTARM obs last within a USUBJID.  But without a minor edit, it would not accommodate two distinct valid values for ACTARM (if a USUBJID had a valid "B" and a valid "C", the "B" would be deleted).   Consider this alternative, applied to data HAVE sorted by USUBJID (but not necessarily by ACTARM within USUBJID):   data want; set have (where=(missing(actarm)=0) in=valid) have (where=(missing(actarm)) in=miss); by usubjid; if lag(valid)=1 and first.usubjid=0 and miss=1 then delete; run;   ... View more

You want a code suggestion.  So help us help you.  Please provide sample data in the form of a DATA step, and show the corresponding desired results.   BTW, what have you tried so far?   Oh yes, and can your dataset have a USUBJID with MORE THAN 2 observations? ... View more

@Winsira wrote: Thank you so much it worked! @Winsira    Then mark @Ksharp's response as the solution.  That will help other users know in advance that a solution is available.  And for those who might be ready with a solution, they don't need to open the topic uselessly.     ... View more

@Bill3 wrote: It seems quite reasonable. So there's no way to combine these two relop in %if? I believe you will have to insert the AND operator, as in: %if 1<&tab. AND &tab.<4 %then %do;     ... View more

Unlike the IF clause in regular SAS data step, the %IF macro statement processes from left to right, which means the expression   %IF 1<&tab.<4 first evaluates the 1<&tab component.   When macrovar &TAB equals 3, this component resolves to 1 (for true).  When macrovar &TAB is, say 0, this component resolves to 0 (for false).   Then it tests whether that resolved value is less than 4, which is also true (because both 0 and 1 are <4).  In fact, for any &TAB value, the %IF 1<&tab.<4 macro test will always be true.         But as you no doubt know, this is NOT the behavior of the non-macro IF statement, which imputes an implicit AND operator when you have the if value1 relop value2 relop value3 structure (where "relop" means relational operator).  So the expression if 1<%tab.<4 is interpreted as  if 1<&tab. AND &tab<4 ... View more

I have no idea whether a Viya-specific solution is available.  But if you run a SAS DATA step, you can use a hash object, with nationality and salary defined as the keys. ... View more

  Read the entire dataset, using the NOBS= parameter with the drop rate to generate the  target drop count for the initial LAST_SEM value.  Then as the target for each semester is reached, update the target through the final eligible LAST_SEM, after which LAST_SEM is set to missing.   data have; do id=1 to 100; output; end; run; %let rate=.1; %let beg_drop=2; %let end_drop=10; data want; retain last_sem &beg_drop _target 0 ; set have nobs=nrecs; if _n_=1 then _target=round(&rate * nrecs); output; if _n_= _target then do; last_sem+1; _target + round(&rate * (nrecs-_n_)); if last_sem>&end_drop then call missing(last_sem,_target); end; run;     Of course, this assumes that the nobs of HAVE and the dropout rate will generate at least one dropout in the first eligible time period.  If that is a possible issue, then:   data want; retain last_sem &beg_drop _target 0 ; set have nobs=nrecs; if _n_=1 then do; _target=round(&rate * nrecs); if _target=0 then call missing(last_sem,_target); end; output; if _n_= _target then do; last_sem+1; _target + round(&rate * (nrecs-_n_)); if last_sem>&end_drop then call missing(last_sem,_target); end; run; ... View more

You can use two arrays, each with 153 elements - one array for the dates, and one for the mme values.  Then just iterate through the 153 elements of each array, test the date and add the mme to the pre- or post- summary value, as appropriate:   data want; array odate order_date_1 - order_date_153; array mme mme_1 - mme_153; do over odate; if odate>index_date then post_sum=sum(post_sum,mme); else if odate>. then pre_sum=sum(pre_sum,mme); end; run; ... View more

@PaigeMiller wrote: So let's see, you have recently posted three different threads on subjects related to coding percentiles. And it feels as if no progress has been made. How about you take a different approach? How about, instead of focusing on how to write specific pieces of code, you go ahead and describe the big problem you are trying to solve. and If you had provided this (and ideally sample data), instead of getting bogged down in certain specific coding issues (which is where we are now), the people here could go ahead and think up a good way to get to the desired results, and show you example code that gets you there (and that works). But we don't really know where you want to end up, and so we can't get you there. In short @Ronein:  Help us help you. ... View more

A minor tweak of the code provided by @ballardw, in the pursuit of tighter code:   data have; input rank min max; cards; 0 0 800 1 1000 1901 2 2000 2425 3 2500 3000 4 3001 4000 5 4015 4935 7 5000 5000 9 5001 6000 10 6001 6480 13 6500 6500 16 6501 6934 17 7000 7200 18 7244 7400 25 7500 7500 run; data want (drop=nxt:); merge have have (firstobs=2 keep=rank rename=(rank=nxt_rank)) ; do rank=rank by 1 until (rank=coalesce(nxt_rank,26)-1); output; end; run; ... View more

I think you want one record per FILE, namely the first non-reception record to follow a reception:   DATA SALE; LENGTH FILE $3 DEPARTMENT $10 ; INPUT FILE DATE:YYMMDD10. DEPARTMENT $; FORMAT DATE YYMMDD10.; DATALINES; 102 2024-01-15 RECEPTION 102 2024-01-30 RECEPTION 102 2024-02-10 SHOES 102 2024-02-29 RECEPTION 102 2024-02-29 SHOES 103 2024-01-22 RECEPTION 103 2024-02-15 RECEPTION 103 2024-02-20 RECEPTION 103 2024-03-02 PANTS 104 2024-04-12 RECEPTION 104 2024-05-02 RECEPTION 104 2024-05-20 RECEPTION 104 2024-05-30 SHIRTS 104 2024-06-14 SHIRTS 104 2024-06-30 SHIRTS RUN; data want; set sale; by file; if first.file^=1 and department^='RECEPTION' and lag(department)='RECEPTION'; if file^=lag(file); run; The first subsetting IF gets all non-receptions that follow a reception.   From that subset, the second subsetting IF takes only the first such record for each FILE. ... View more

If you really need only the names of datasets with at least one instance of the value, you can build a SET statement with all the candidate datasets, but use OBS=1 to tell SAS to stop processing a dataset once an instance has been encountered.  Looking for variable X=2001 in the code below:   data xxx; /*Four instances of x=2001 */ do x=1111 to 2222; output; end; x=2001; output; output; output; run; data yyy; /*One instance of x=2001 */ do x=2222 to 1111 by -1; output; end; run; data zzz; /*No variable X */ do y= 1111 to 2222; output; end; run; proc sql noprint; create table need as select libname, memname, catx('.',libname,memname) as dsn from dictionary.columns where libname='WORK' and upcase(name)='X' ; create table candidates as select n.*, t.crdate from need as N left join dictionary.tables as T on n.libname=t.libname and n.memname=t.memname; drop table need; select distinct catx(' ',dsn,"(keep=x where=(x=2001) obs=1)") into :dsn_list separated by ' ' from candidates ; quit; %put &=dsn_list ; data want; set &dsn_list indsname=indsn ; if _n_=1 then do; if 0 then set candidates; declare hash h (dataset:'candidates'); h.definekey('dsn'); h.definedata(all:'Y'); h.definedone(); end; h.find(key:indsn); put (_all_) (=); run;   ... View more

Let's generalize a bit.  Say you have integer values that range from -3  0 to 15, but you only want counts of 0's, 4's, 8's, and 14's.  Then you could     data test; input a b c d; cards; 1 0 9 1 1 1 0 0 0 9 1 1 0 0 9 1 1 0 9 9 0 1 1 0 1 9 9 1 1 9 0 0 0 0 9 1 9 1 0 0 5 6 14 15 8 12 4 0 run; data want (drop=_:); set test; array values a -- d; array counts {0:15} count_0 _dum1 - _dum3 count_4 _dum5 - _dum7 count_8 _dum9 - _dum13 count_14 _dum15 ; do _v=1 to dim(values); counts{values{_v}}=sum(counts{values{_v}},1); end; /*convert missing counts to zeroes */ array cnts count_: ; /* Was mistakenly array cnts n_: ; */ do over cnts; cnts=coalesce(cnts,0); end; run;   ... View more