Hi @cnn96  I think this will do it. First use a select to calculate the time_span for each PatientID then add the calculated value with a join.  Note: Untested, because you didn't provide a data step to generate test data.    proc sql; create table b as select distinct PatientID, count(*) as time_span from mylib.mytable group by PatientID; quit; proc sql; create table table_timespan as select a.*, b.time_span from mylib.mytable as a, b on a.PatientID = b.PatientID; quit; ... View more

Hi @ajb    Another solution. Note that &varlist gets a leading and trailing quote before the tranwrd function is applied. I works anyway, but gives annoying warning notes if done outside tranwrd.   %let varlist = aaaaa bbbbb ccccc zzzzz; %let newvarlist= %sysfunc(tranwrd(%str(%')&varlist%str(%'),%str( ),%str(' ')))%str(%'); %put &=newvarlist; 23 %let varlist = aaaaa bbbbb ccccc zzzzz; 24 %let newvarlist= %sysfunc(tranwrd(%str(%')&varlist%str(%'),%str( ),%str(' '))); 25 %put &=newvarlist; NEWVARLIST='aaaaa' 'bbbbb' 'ccccc' 'zzzzz' ... View more

 @chinna0369    I agree with @ballardw . Not worth the effort.   There is no way to determine the sort order from a data set that ihas not previously been sorted by SAS, so always us a Proc Sort in cases where sorting is required. If a data set has previously been sorted by SAS, the procdure is smart enough to omit sorting when data are already sorted in the wanted sequence, so It comes with no cost besides coding a couple of lines.   It is mandatory in good coding practice to make sure that a data set is sorted before any use that requires sorting, which means all Data Steps with first-last variables, retain, lag or merge, and most SAS Procedures, except Proc SQL. Always use a Proc Sort before these steps.    The reverse applies to the order of variables. Don't put any effort in bringing variables in a specific order while preparing data. Leave that until you come to creating reports or exporting data to Excel.  ... View more

Hi @PaigeMiller    Thanks for the answer. I know that code can be deployed from SAS Studio, and that there are possibilities for scheduling jobs and defining time-events and file-events as triggers, though I haven't done any experiments yet. But there are still a lot of unanswered questions. I apologize for bothering the community with a full novel, but I have done my best to focus on some major problems and omit a lot of minor details and problem areas.   Organizing objects In our DI Studio Folder Tree, objects are organized in data areas. Each data area is a folder with subfolders for data produced in the area (tables and libraries), jobs producing these tables (with deployed jobs and the corresponding flow) and external files read/written by the jobs. The data areas are organized in hierarchies with permissions set at top level, and physical storage is organized in the same structure with inherited permissions.   This structure contains 2.629 folders in 785 data areas, and they contain about 21.000 objects (Jobs, Deployed Jobs, Libraries, Tables and external files – today’s count). We don't know how to maintain a similar easily manageable structure in SAS Studio, where each user has a limited view and access to physical data based on permissions, about 100 different AD Groups. We don't even know if it is possible to control logical access i SAS Studio based on AD Groups as we do today.   Jobs and flows For maintenance reasons, we try to keep jobs small, meaning no more than about 50 DI Studio Transformations in a single job, preferably less, and only a single or a few tables as output. Then we build flows in SAS Management console, where we use a flow as a "running unit", a group of interconnected jobs with internal dependencies. A flow corresponds to one data area in the DI Studio Folder Tree, so the Folder Tree is the skeleton on which everything hangs.   We use LSF as a convenient way to get flows executed in a SAS Grid server cluster with load balancing, but we don't use Process Manager/LSF as a scheduler. The reason is that Process Manager cannot handle triggering based on previous flow events, only time events and file events. Of the 785 flows counted today, 114 are "initial" flows, meaning they are not dependent on results from previous flows and can be started based on time events only. The rest, 671 flows, depend on results from previous flows (mostly several flows) in a complicated hierarchy, where a chain can be more than 15 flows long, and a given chain can contain scores of previous flows.   This cannot be maintained manually, especially with a change rate of 220 new/changed jobs and 35 new/changed flows as a weekly average. We have built a scheduler that initiate each daily batch with building a virtual "super-flow" out of all flows that have the current day set as running date, and then proceeds with releasing or excluding flows based on previous results. It is fully automatic, so there is no manual charting of dependencies between flows or defining of triggers involved. The mechanism is based on table lineage extracted from SAS Metadata at batch start-time,   The structure has been allowed to grow into such complexity over the years because there is no manual work involved, so it has being going on without anybody realizing that it might be difficult to maintain in other environments. We use about 20 hours per week to promote jobs to production, schedule jobs and define flows, monitor batch execution, identify and correct errors, rerun failed flows etc. Thanks to automation of all processes from promotion to monitoring, this has been a constant over 12 years while our Data Warehouse has grown with a factor 10 or more.    And what now This boils down to five technologies that we use today to run our Data Warehouse, and it seems that SAS Viya does not offer a similar functionality to any of them, except (maybe) no. 2. I have underlined what I consider to be the primary outcome of each technology, when it comes to building a similar batch environment in SAS Viya.   Deployment in DI Studio with automated generation of command strings to execute jobs. Building Process Manager Flow Definitions in SAS MC with internal job dependencies. Command-line execution of Flow Definitions by Process Manager.  Automated load balancing with LSF /Grid Manager in a server cluster with a shared file system.  Automatic charting of flow dependencies through lineage maintained in SAS Metadata.   Our current use of about 20 hours per week to maintain and run the batch environment with many daily changes makes us anxious. It will be difficult to get senior management to accept that migration to a new and hyped platform has a price. It will be hard to obtain sufficient resources for migration while getting the existing setup running smoothly in parallel, and even harder to get them to realize that the new and smart platform might be a setback to the old mainframe days requiring 10 employees in a separate operating team.   ... View more

Hi @AhmedAl_Attar    Thanks for answering. We are in the initial stages of planning a migration to Viya. We don't really see any benefits from migration except moving to a platform with no announced end-of-support. This last has been a topic for discussion, but our information so far is that we cannot expect further functionsl updates to the SAS9 platform or support for newer Redhat versions, only security updates, which will end in 2028. So we are in no hurry, but we might be facing a migration process that will take years, so we need to start planning right now.   Our data preperation platform is SAS9. We do have SAS Visual Analytics with an underlying Viya 3.5 today, but if (when) we migrate from SAS9, we will migrate everything to the current Viya version at that time. So we are planning with Viya 4 and on-premise hosting. ... View more

Hi @Ronein    The conversion can be done i one statement with the same set of functions. There is no need for intermediate variables and if-then conditions. Note the leading ?? in the yymmdd8 format. The result is set to missing if an invalid date value (incl 0) is encountered, but all the notes "Invalid argument to function INPUT at line ..." are suppressed.  data have; input YYYYMMDD ; cards; 20230327 20230324 0 0 20230322 ; run; data want; set have; want_date=compress(put(input(put(YYYYMMDD,8.),??yymmdd8.),DDMMYY10.),'.'); run; When it comes to make this dynamic, so it can handle an unknown set of input variables, there is a problem with naming the output variables, because it is difficult to create new variables in a data step with names derived from unknown input variables, not just wanted_1-wanted_n.   In this example, the original variables are dropped, because there is no easy way to drop them afterwards without writing them as literals in a data step. The original variable names are reused for the new character variables in output, so it has the same number of variables with the same names and order, but with different type and content.   I started out with building variable lists in macro variables, but it became rather big and complicated, so I changed to a call execute-approach. Using a data step to write another data step this way is a very powerful tool, as you can see in the following example:   data have2; input YYYYMMDD1 1-8 date2 10-18 date 19-27 X4 ; cards; 20230327 20230327 20230322 20230322 20230324 0 20230322 20230322 0 20230322 0 0 0 0 0 0 20230322 20230322 0 20230322 ; Run; data _null_; set have2 (obs=1); array numdate{*} _numeric_; length ilist vlist $200;*48.; do i = 1 to dim(numdate); ilist = catx(' ', ilist, vname(numdate{i})); vlist = catx(' ', vlist, catt('want_', vname(numdate{i}))); end; call execute(catx(' ', 'data want2 (drop=i', ilist, '); set have2;')); call execute('array numdate{*} _numeric_;'); call execute(catx(' ', 'array chardate {*} $10', vlist, ';')); call execute('do i = 1 to dim(numdate);'); call execute('chardate{i} = compress(put(input(put(numdate{i},8.),??yymmdd8.),DDMMYY10.),".");'); call execute('end; run;'); run; I had a lot of fun out of this, thank you!       ... View more

Hi @analyticsguy23    I haven't tried it, but I think ihe easiest way would be to write data from SAS to a database table and read the database table from DOMO. There are several prerequisites for this: A database (MSSQL, MySQL or PostgreSQL) that is accessible from both SAS and DOMO. A running SAS platform with a separately licensed Access Module for the database. A DOMO license for the proper Database Connector.   An alternative, that would work without a database and a SAS Access Module, could be to export from SAS to a CSV file and import it in DOMO using a CSV Connector. There prerequisites here are: A file system that is accessible from both SAS and DOMO (might be the tricky part) A running SAS platform  A DOMO license for the CSV Connector.     ... View more

Hi @s_lassen    You wrote I do not understand why you think it is so important to keep the MPRINT output and the actual macro variable values unreadable.   We normally run production jobs with NOMPRINT, and MPRINT is only activated for debugging. That's why I like to put macro variable values in a human-readable form, so they appear in the log anyway and can be used in a first-level-debugging without having to read thousands of MPRINT lines.   And I really see date constants in macro variables as a nuisance. A SAS Date value will always work, but a date constant in a macro variable might give all kinds of trouble. It cannot be used in a macro calculation, and the presence of quotes in the value can also be troubleome, e.g. when building strings for CALL Execute using CAT-functions.   Consider this example:   161 %let date_start = "%sysfunc(intnx(month,%sysfunc(today()),-3,b),date9.)"d; 162 %let date_start = %eval(&date_start + 7); ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: "01DEC2022"d + 7 163 %put &=date_start; DATE_START= 164 165 %let date_start = %sysfunc(intnx(month,%sysfunc(today()),-3,b)); 166 %let date_start = %eval(&date_start + 7); 167 %put %sysfunc(putn(&date_start,date9.)); 08DEC2022   Best greetings Erik           ... View more

Hi @David_Billa    In SAS, dates should never be kept in a data set as text values, but always converted to SAS Date values. Similar with macro variables, they should also contain SAS Date values unless they are created for the sole purpose of being human-readable, e.g. as part of a title statement. This way the macro variables can always be used in where-statements, data step functions etc. without any conversion, so the coding becomes simpler and less error-prone.   But it is also true that it is nice to have the date values written to the log in a readable form, as @s_lassen  advocates. It is always a good practice to write a macro variable to the log when it has been assigned a value, and with a little effort, one can have both:   %let date_start = %sysfunc(intnx(month,%sysfunc(today()),-3,b)); %put &=date_start (%sysfunc(putn(&date_start,date9.))); DATE_START=22980 (01DEC2022)   ... View more

Hi @dandsouza    Sorry, forgot to copy a ; before the first run; - this hopefullky looks better:   data have; input filename :$20. date :date9.; format date yymmdd10.; datalines; IncomeCalc 01Dec2021 IncomeCalc 02Dec2021 IncomeCalc 03Dec2021 IncomeCalc 04Dec2021 IncomeCalc 05Dec2021 IncomeCalc 06Dec2021 IncomeCalc 07Dec2021 DonateCalc 07Dec2021 ExpenseCalc 15Dec2021 IncomeCalc 08Dec2021 IncomeCalc 09Dec2021 IncomeCalc 19Dec2021 IncomeCalc 11Dec2021 IncomeCalc 12Dec2021 IncomeCalc 13Dec2021 IncomeCalc 14Dec2021 DonateCalc 07Dec2021 ExpenseCalc 30Dec2021 ; run; /* Step 1 - calculate the count of days since last run + the number of times a given filename reoccurs. */ proc sort data=have; by filename date; run; data interval (drop=date_last intervals_total) files (drop=date date_last interval); set have; by filename date; if first.filename then intervals_total = 0; intervals_total + 1; date_last = lag(date); if not first.filename then do; interval = date-date_last; output interval; end; if last.filename then do; intervals_total = intervals_total - 1; output files; end; run; /* Step 2 - For each combination of filename / day_count, calculate the number of occurrences. */ proc sql; create table interval_count as select filename, interval, count(*) as intervals_found from interval group by filename, interval order by filename, intervals_found ; quit; /* Step 3 - For each combination of filename / day_count, calculate the percentage of this day_count.*/ data want_intermediate; merge interval_count files; by filename; format pct_occurred 5.0; pct_occurred = (intervals_found * 100) / intervals_total; run; /* Step 4 - For each filename, take the most frequently occurring day_count and translate the day_count to a scheduling period. */ data want_final; set want_intermediate; by filename; length schedule $20; if last.filename then do; if 86 < interval < 33 then schedule = 'Quarterly'; else if 26 < interval < 33 then schedule = 'Monthly'; else if 12 < interval < 16 then schedule = 'SecondWeek'; else if interval = 7 then schedule = 'Weekly'; else if interval = 1 then schedule = 'Daily'; else schedule = 'Undetermined'; output; end; run; ... View more

Hi @dandsouza    I will suggest a slightly more complex approach: For each reoccuring filename, calculate the count of days since last run + the number of times a given filename reoccurs. For each combination of filename / day_count, calculate the number of occurrences. For each combination of filename / day_count, calculate the percentage of this day_count. For each filename, take the most frequently occurring day_count and translate the day_count to a scheduling period. The translation in step 4 may need some refinement based on the result from step 3. it is difficult to figure out from the provided data example. data have; input filename :$20. date :date9.; format date yymmdd10.; datalines; IncomeCalc 01Dec2021 IncomeCalc 02Dec2021 IncomeCalc 03Dec2021 IncomeCalc 04Dec2021 IncomeCalc 05Dec2021 IncomeCalc 06Dec2021 IncomeCalc 07Dec2021 DonateCalc 07Dec2021 ExpenseCalc 15Dec2021 IncomeCalc 08Dec2021 IncomeCalc 09Dec2021 IncomeCalc 19Dec2021 IncomeCalc 11Dec2021 IncomeCalc 12Dec2021 IncomeCalc 13Dec2021 IncomeCalc 14Dec2021 DonateCalc 07Dec2021 ExpenseCalc 30Dec2021 run; /* Step 1 - calculate the count of days since last run + the number of times a given filename reoccurs. */ proc sort data=have; by filename date; run; data interval (drop=date_last intervals_total) files (drop=date date_last interval); set have; by filename date; if first.filename then intervals_total = 0; intervals_total + 1; date_last = lag(date); if not first.filename then do; interval = date-date_last; output interval; end; if last.filename then do; intervals_total = intervals_total - 1; output files; end; run; /* Step 2 - For each combination of filename / day_count, calculate the number of occurrences. */ proc sql; create table interval_count as select filename, interval, count(*) as intervals_found from interval group by filename, interval order by filename, intervals_found ; quit; /* Step 3 - For each combination of filename / day_count, calculate the percentage of this day_count.*/ data want_intermediate; merge interval_count files; by filename; format pct_occurred 5.0; pct_occurred = (intervals_found * 100) / intervals_total; run; /* Step 4 - For each filename, take the most frequently occurring day_count and translate the day_count to a scheduling period. */ data want_final; set want_intermediate; by filename; length schedule $20; if last.filename then do; if 86 < interval < 33 then schedule = 'Quarterly'; else if 26 < interval < 33 then schedule = 'Monthly'; else if 12 < interval < 16 then schedule = 'SecondWeek'; else if interval = 7 then schedule = 'Weekly'; else if interval = 1 then schedule = 'Daily'; else schedule = 'Undetermined'; output; end; run;               ... View more

Hi @Quentin    It works in Linux but not in Windows, so I think you are right in assuming it is a bug.        ... View more

Hi @SASMom2  As noticed by @PaigeMiller , your first statement is syntactically wrong, because you cannot combine two %if conditions with an OR-operator.   You don't need the double quotes, because they are not acting as quotes in the macro language. They are treated as text and becomes part of the value. So when used on both sides in the equation,the result will be the same.   You can use the trailing . after a macro variable name, but it not necessary. It acts as a delimiter between a macro variable name and something immediately following it, so this something is not seen as part of the macro variable name.   As for your second statement, you can also use the in operator in macro language, but you need a MINOPERATOR option to tell the macro compiler that it should accept it. So these four macros all work and give the same result:   %macro typ(cust=); %if "&cust"="abc" or "&cust"="def" %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %macro typ(cust=); %if &cust=abc or &cust=def %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %macro typ(cust=) / minoperator; %if "&cust." in ("abc" "def") %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %macro typ(cust=) / minoperator; %if &cust. in (abc def) %then %put TRUE - Cust is &cust; %else %put FALSE - Cust is &cust; %mend; %typ(cust=abc) ; %typ(cust=def) ; %typ(cust=DEF) ; %typ(cust=ghi) ;   ... View more

Hi @neyoushah  To elaborate on the post from @Quentin    SAS is basically a statistical package with procedures that works on SAS Data sets, and the data step is originally intended as a tool to get data into SAS from external sources. And while it is true that almost anything can be done with a single complicated data step, is is not always the best approach.   This forum is about programming, so it is natural that most problems are presented as programming problems, i.e. how to get a result based on the idea that a solution starts with the token data and ends with the token run, and that most answers are given within that same context.   Unfortunately there is no forum to address overall program design, meaning how to write a whole program, not a data step, to solve a given problem with SAS software. A data step can easily be so complicated that nobody (including the programmer herself after a week) can figure out what's going on.   Start with getting an understanding of what you have. In this case it seems that there is one record per patient, and that each D_I10_ column represents a visit with one diagnosis code per visit, which may be alcohol related. If more than one column with the same code is present, they represent one visit each. If you are in doubt, or your interpretation in any way conflicts with actual data, get back to the source of data to verify it. Then define your final goal. What is it you (or the one requesting it) wants to know based on data. Which final data structure will be best to obtain that.   Next step is to get data into SAS. If the original source is a spreadsheet, then write a data step to read it into SAS. Just get it in, leave further data manipulation to later. And don't rely on Proc Import for this.   Then consider what additional information you need. In this case it is a list of alcohol-related diagnosis codes. Don't think of hardcoding them as constants in select-constructs or if-then sequences. Get them into a SAS data set too, so they are ready for further use, if necessary as cards in a data step.   Now consider your program flow. Don't be afraid of multiple steps, it is much better to break the problem down so it can be solved with a series of steps, where you for each step can chosse the best tool for that. Way back "real programmers" mocked SAS as meaning Sort-After-Sort, but it's the way it works, and what makes it much faster to get a result with SAS than with more code-oriented languages. So use it as intended.   First thing is to get rid of the Excel-structure, which has no place in SAS, so transpose your SAS input from wide to long. Use Proc Transpose because it is simpler than traversing arrays in a data sptep. The transposed data set is much easier to handle, now you can drop unwanted diagnisis codes with a simple Proc SQL join or a Data step merge, whichever you prefer, because you have another data set with the list of relevant codes.   Then consider the next step. Is it the number of alcohol-related visits per patient you want, the number of visits per diagnosis code, or the number of different diagnosis coder per patient. If it is more than one of these, then split up from here and treat different wishes as individual subtasks. It is much easier to code, if you can get the result with a simple count in Proc SQL og a Proc Means. This approach gives a program that is much easier to code and understand, easier to maintain and easier to add additional functionaly to. Just add other steps to fulfill further requests.   I have so many old programs running today where I deeply regret that I started out with coding instead of stepping back to make a proper design based on an understanding of data. The time spent on planning comes back twice in the coding phase. Besides, it is much easier to verify that the code works as intended, because there are intermediate result to check and small pieces of code to debug and correct. So don't be afraid of multiple steps. And remember that the core procedures are parts of the SAS language too, and that a good understanding of them is an integral part of being a good SAS programmer.   ... View more